package com.pangus.ims.mes.service;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Future;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFCreationHelper;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.jsoup.helper.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.pangus.common.dto.ConditionDto;
import com.pangus.common.dto.EditDataResult;
import com.pangus.common.dto.EditResult;
import com.pangus.common.dto.JqGridRequest;
import com.pangus.common.dto.JqGridResponse;
import com.pangus.common.dto.JsonRequest;
import com.pangus.common.excel.CheckExcelResult;
import com.pangus.common.excel.callback.ExcelCallbackCommonService;
import com.pangus.common.sys.utils.CheckUtils;
import com.pangus.common.sys.utils.CopyUtils;
import com.pangus.common.sys.utils.FileUploadUtils;
import com.pangus.common.sys.utils.ParamUtils;
import com.pangus.common.sys.utils.SqlQueryUtils;
import com.pangus.error.exception.BaseException;
import com.pangus.fa.i18n.FaMessageSource;
import com.pangus.fa.log.Log;
import com.pangus.fa.mybatis.mapper.entity.Condition;
import com.pangus.fa.mybatis.mapper.entity.Condition.Criteria;
import com.pangus.fa.util.StringUtils;
import com.pangus.ims.bs.dto.domain.BsMnLpnDto;
import com.pangus.ims.bs.form.BsMnLpnForm;
import com.pangus.ims.mes.dto.domain.BsCustShipmentDto;
import com.pangus.ims.mes.dto.domain.MesAgeDevBeforeDto;
import com.pangus.ims.mes.dto.domain.MesAgeDevBeforePassDto;
import com.pangus.ims.mes.dto.domain.MesAgingAfterInfoDto;
import com.pangus.ims.mes.dto.domain.MesCustShipmentDto;
import com.pangus.ims.mes.dto.domain.MesIpcLogDto;
import com.pangus.ims.mes.dto.domain.MesMoLbDto;
import com.pangus.ims.mes.form.BsCustShipmentForm;
import com.pangus.ims.mes.form.MesCustShipmentFormDt;
import com.pangus.ims.mes.form.MesMoLbAcclbForm;
import com.pangus.ims.mes.utils.CustShipmentTransUtils;
import com.pangus.ims.streamax.dto.domain.StreamaxIpMacDto;
import com.pangus.ims.streamax.service.StreamaxIpMacService;
import com.pangus.ims.wms.dto.domain.WmsCustShipmentDto;
import com.pangus.ims.wms.dto.domain.WmsMnDto;
import com.pangus.ims.wms.service.WmsMnProdSellByLpnPdaService;

@Service
public class BsCustShipmentService implements ExcelCallbackCommonService<BsCustShipmentForm>{

	private static final Logger logger = LoggerFactory.getLogger(BsCustShipmentService.class.getName());
	@Autowired
	private FaMessageSource messageSource;
	@Autowired
	private SqlQueryUtils sqlQueryUtils;
	@Autowired
	private MesMoLbService mesMoLbService ;
	@Autowired
	private MesAgingAfterInfoService agingAfterInfoService;
	@Autowired
	private MesAgeDevBeforeService ageDevBeforeService;
	@Autowired
	private MesIpcLogService mesIpcLogService;
	@Autowired
	private BsCustShipmentForPmnCodeService bsCustShipmentForPmnCodeService;
	@Autowired
	private MesCustShipmentService mesCustShipmentService;
	@Autowired
	private WmsMnProdSellByLpnPdaService wmsMnProdSellByLpnPdaService;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	 @Autowired
	private StreamaxIpMacService streamaxIpMacService;
	
	@Autowired
	private HttpServletRequest thisRequest;
	
	@Log("apiLogger")
	private Logger apiLogger;
	
	@Autowired
	private MesAgeDevBeforePassService ageDevBeforePassService;
	
	private static Map<String, List<JqGridRequest<BsCustShipmentForm>>> importData  = new Hashtable<String, List<JqGridRequest<BsCustShipmentForm>>>();;

	final static String AFTER_SQL = "SELECT CHIP_NUMBER AS MAIN_CHIP_NO,  TERMINAL_NO ,MAC_ADDRESS AS MAC,"
			+ "WIFI_MAC, CHAT_IMEI AS IMEI, PHONE_NUMBER  "
			+ " FROM MES_AGING_AFTER_INFO WHERE LB_ID = ? AND MO_ID = ? AND IS_PASS='Y' "
			+ "AND CREATED_DATE BETWEEN ? AND ? ";

	final static String BEFORE_SQL = "SELECT BEFORE_CHIP AS MAIN_CHIP_NO, BEFORE_MAC AS MAC, BEFORE_IMEI1 AS IMEI  "
			+ " FROM MES_AGE_DEV_BEFORE WHERE BEFORE_BARCODE = ? AND MO_ID = ? AND VALID_FLAG = '1' AND BEFORE_RESULT = '1' "
			+ "AND CREATED_DATE BETWEEN ? AND ? ";

	final static String TF_SQL = "SELECT SRC_LB_ID AS FIRST_LB_ID FROM MES_MO_LB WHERE LB_ID = ? AND MO_ID = ? ";

	final static String WEIGHT_SQL = "SELECT NET_WEIGHT, GROSS_WEIGHT, GROSS_WEIGHT_MNUIT AS MUNIT "
			+ "FROM MES_MO_LBWP WHERE LB_ID = ? AND MO_ID = ? "
			+ "AND CREATED_DATE BETWEEN CURRENT_DATE-183 AND CURRENT_DATE+1  " + " ORDER BY CREATED_DATE DESC";

	final static String WEIGHT_SQL_WITHDATE = "SELECT M.NET_WEIGHT, M.GROSS_WEIGHT, M.GROSS_WEIGHT_MNUIT AS MUNIT "
			+ "FROM MES_MO_LBWP M "
			+ "LEFT JOIN BS_MO_TPWP W ON W.BS_MO_TPWP_ID = M.BS_MO_TPWP_ID "
			+ "WHERE M.LB_ID = ? AND M.MO_ID = ? "
			+ "AND W.TPWP_NAME LIKE '%称重%' "
			+ "ORDER BY M.CREATED_DATE DESC";

	final static String IPC_SQL = "SELECT SERIAL_NUM, MAC,IMEI FROM MES_IPC_LOG WHERE LB_ID =? AND MO_ID = ? "
			+ "AND RESULT='Y' AND CREATED_DATE BETWEEN ? AND ?  ";
	
	final static String ACCLB_SQL = "SELECT B.ACCLB_ID, B.ACCLBTYPE_ID, B.LB_ID, S.MTRL_ID, M.MTRL_CODE , M.MTRL_NAME "
			+ "FROM MES_MO_LB_ACCLB B "
			+ "LEFT JOIN BS_MO_PROD_LB_SUB S ON S.MO_ID = B.MO_ID AND S.SUB_LBTYPE_ID = B.ACCLBTYPE_ID "
			+ "LEFT JOIN BS_MTRL M ON M.MTRL_ID = S.MTRL_ID "
			+ "WHERE B.LB_ID = ? AND B.MO_ID = ? "
			+ " ORDER BY B.CREATED_DATE ";

	private CheckUtils checkUtils;

	public JqGridResponse<BsCustShipmentForm> query(JqGridRequest<BsCustShipmentForm> request) {
		List<ConditionDto> conditions = request.getCondition();
		JqGridResponse<BsCustShipmentForm> result = new JqGridResponse<>();
		result.setTotalPage(0);
		result.setTotalSize(0);
		if (conditions == null || conditions.size() == 0) {
			return result;
		}
		String moId = null;
		String pmnCode = null;
		String sn = null;
		String mac = null;
		String chipNumber = null;
		String imei = null;
		String prodCode = null;
		for (ConditionDto conditionDto : conditions) {
			switch (conditionDto.getConditionColumn()) {
			case "moId":
				moId = conditionDto.getColumnValue();
				break;	
			case "pmnCode":
				pmnCode = conditionDto.getColumnValue();
				break;	
			case "sn":
				sn = conditionDto.getColumnValue();
			case "mac":
				mac = conditionDto.getColumnValue();
			case "chipNumber":
				chipNumber = conditionDto.getColumnValue();
			case "imei":
				imei = conditionDto.getColumnValue();
			case "prodCode":
			    prodCode = conditionDto.getColumnValue();
			default:
				break;
			}
		}
		if(!StringUtils.isBlank(pmnCode)) {
			return bsCustShipmentForPmnCodeService.queryByPmnCode(pmnCode);
		}
		if (!StringUtils.isBlank(moId) ) {
			return queryByMoId(moId);
		}
		if (!StringUtils.isBlank(sn) ) {
			return queryBySn(sn);
		}
		if (!StringUtils.isBlank(mac) ) {
			return queryByMac(mac);
		}
		if (!StringUtils.isBlank(chipNumber) ) {
			return queryByChipNumber(chipNumber);
		}
		if (!StringUtils.isBlank(imei) ) {
			return queryByImei(imei);
		}
		if (!StringUtils.isBlank(prodCode) ) {
		    return bsCustShipmentForPmnCodeService.queryByProdCode(prodCode);
        }
		return result;
	}

	
	private StringBuilder sqlAgingAfter = new StringBuilder("SELECT MO_ID, LB_ID FROM MES_AGING_AFTER_INFO WHERE  IS_PASS = 'Y' ");
	private StringBuilder sqlAgingBefore = new StringBuilder("SELECT MO_ID,BEFORE_BARCODE LB_ID FROM MES_AGE_DEV_BEFORE"
			+ " WHERE  VALID_FLAG = '1' AND BEFORE_RESULT = '1' ");
	private StringBuilder sqlIpc =  new StringBuilder("SELECT MO_ID,LB_ID FROM MES_IPC_LOG WHERE  RESULT = 'Y' ");
	
	public JqGridResponse<BsCustShipmentForm> queryByImei(String imei) {
		StringBuilder sqlAgingAfter2 = new StringBuilder(sqlAgingAfter) ;
		sqlAgingAfter2.append(" AND CHAT_IMEI = ? ");
		Object[] objects = new Object[] {imei};
		
		//查询老化后
		MesMoLbDto mesMoLbDto = sqlQueryUtils.get(sqlAgingAfter2.toString(), objects, MesMoLbDto.class);
		if(mesMoLbDto == null) {
			//查询老化前
			StringBuilder sqlAgingBefore2 = new StringBuilder(sqlAgingBefore) ;
			sqlAgingBefore2.append("AND BEFORE_IMEI1 = ?");
			mesMoLbDto = sqlQueryUtils.get(sqlAgingBefore2.toString(), objects, MesMoLbDto.class);
			if(mesMoLbDto == null) {
				//查询IPC
				StringBuilder sqlIpc2 = new StringBuilder(sqlIpc) ;
				sqlIpc2.append("AND IMEI = ?");
				mesMoLbDto = sqlQueryUtils.get(sqlIpc2.toString(), objects, MesMoLbDto.class);
			}
		}
		if(mesMoLbDto == null) {
			JqGridResponse<BsCustShipmentForm> result = new JqGridResponse<>();
			result.setTotalPage(0);
			result.setTotalSize(0);
			return result;
		}
		String lbId = mesMoLbDto.getLbId();
		return queryBySn(lbId);
	}

	public JqGridResponse<BsCustShipmentForm> queryByChipNumber(String chipNumber) {
		StringBuilder sqlAgingAfter2 = new StringBuilder(sqlAgingAfter) ;
		sqlAgingAfter2.append(" AND CHIP_NUMBER = ? ");
		Object[] objects = new Object[] {chipNumber};
		
		//查询老化后
		MesMoLbDto mesMoLbDto = sqlQueryUtils.get(sqlAgingAfter2.toString(), objects, MesMoLbDto.class);
		if(mesMoLbDto == null) {
			//查询老化前
			StringBuilder sqlAgingBefore2 = new StringBuilder(sqlAgingBefore) ;
			sqlAgingBefore2.append("AND BEFORE_CHIP = ?");
			mesMoLbDto = sqlQueryUtils.get(sqlAgingBefore2.toString(), objects, MesMoLbDto.class);
			if(mesMoLbDto == null) {
				//查询IPC
				StringBuilder sqlIpc2 = new StringBuilder(sqlIpc) ;
				sqlIpc2.append("AND SERIAL_NUM = ?");
				mesMoLbDto = sqlQueryUtils.get(sqlIpc2.toString(), objects, MesMoLbDto.class);
			}
		}
		if(mesMoLbDto == null) {
			JqGridResponse<BsCustShipmentForm> result = new JqGridResponse<>();
			result.setTotalPage(0);
			result.setTotalSize(0);
			return result;
		}
		String lbId = mesMoLbDto.getLbId();
		return queryBySn(lbId);
	}

	
	public JqGridResponse<BsCustShipmentForm> queryByMac(String mac) {
		StringBuilder sqlAgingAfter2 = new StringBuilder(sqlAgingAfter) ;
		sqlAgingAfter2.append(" AND MAC_ADDRESS = ? ");
		Object[] objects = new Object[] {mac};
		
		//查询老化后
		MesMoLbDto mesMoLbDto = sqlQueryUtils.get(sqlAgingAfter2.toString(), objects, MesMoLbDto.class);
		if(mesMoLbDto == null) {
			//查询老化前
			StringBuilder sqlAgingBefore2 = new StringBuilder(sqlAgingBefore) ;
			sqlAgingBefore2.append("AND BEFORE_MAC = ?");
			mesMoLbDto = sqlQueryUtils.get(sqlAgingBefore2.toString(), objects, MesMoLbDto.class);
			if(mesMoLbDto == null) {
				//查询IPC
				StringBuilder sqlIpc2 = new StringBuilder(sqlIpc) ;
				sqlIpc2.append("AND MAC = ?");
				mesMoLbDto = sqlQueryUtils.get(sqlIpc2.toString(), objects, MesMoLbDto.class);
			}
		}
		if(mesMoLbDto == null) {
			JqGridResponse<BsCustShipmentForm> result = new JqGridResponse<>();
			result.setTotalPage(0);
			result.setTotalSize(0);
			return result;
		}
		String lbId = mesMoLbDto.getLbId();
		return queryBySn(lbId);
	}

	public JqGridResponse<BsCustShipmentForm> queryBySn(String sn) {
		String boxSql = BsCustShipmentForPmnCodeService.BOX_SQL;
		String boxSql2 = BsCustShipmentForPmnCodeService.BOX_SQL2;
		String mateSql = BsCustShipmentForPmnCodeService.MATE_SQL;
		String snSql = BsCustShipmentForPmnCodeService.SN_SQL;
		String wmnSql = "SELECT W.PMN_CODE, W.SHIP_DATE FROM BS_MN_LPN N INNER JOIN WMS_MN W ON W.PMN_CODE = N.MN_ID WHERE N.LPN = ? ";
		//查询是不是箱号数据
		List<MesCustShipmentDto> mesCustShipmentDtos = sqlQueryUtils.queryAll(boxSql, new Object[] {sn}, MesCustShipmentDto.class);
		if(mesCustShipmentDtos != null && mesCustShipmentDtos.size() >0) {
			List<BsCustShipmentForm> data = new ArrayList<BsCustShipmentForm>();
			//查询箱号出货单
			WmsMnDto wmsMnDto = sqlQueryUtils.get(wmnSql, new Object[] {sn}, WmsMnDto.class);
			for (MesCustShipmentDto mesCustShipmentDto : mesCustShipmentDtos) {
				String id = mesCustShipmentDto.getId();
				List<MesCustShipmentFormDt> mateList = sqlQueryUtils.queryAll(mateSql, new Object[] {id}, MesCustShipmentFormDt.class);
				if(wmsMnDto == null) {
					//查询SN出货单
					if(!StringUtils.isEmpty(mesCustShipmentDto.getTfSn())) {
						wmsMnDto = sqlQueryUtils.get(wmnSql, new Object[] {mesCustShipmentDto.getTfSn()},  WmsMnDto.class);
					}else {
						wmsMnDto = sqlQueryUtils.get(wmnSql, new Object[] {mesCustShipmentDto.getMainSn()},  WmsMnDto.class);
					}
				}
				List<BsCustShipmentForm> bsCustShipmentForms = CustShipmentTransUtils.toBsCustShipmentFormList(mesCustShipmentDto,
						mateList, wmsMnDto);
				data.addAll(bsCustShipmentForms);
			}
			JqGridResponse<BsCustShipmentForm> jqGridResponse = new JqGridResponse<BsCustShipmentForm>();
			jqGridResponse.setData(data);
			jqGridResponse.setPageNo(1);
			jqGridResponse.setTotalPage(1);
			jqGridResponse.setTotalSize(data.size());
			return jqGridResponse;
		}else {
			//查询是不是SN
			MesCustShipmentDto mesCustShipmentDto = sqlQueryUtils.get(snSql, new Object[] {sn, sn}, MesCustShipmentDto.class);
			if(mesCustShipmentDto != null) {
				List<BsCustShipmentForm> data = new ArrayList<BsCustShipmentForm>();
				//查询SN出货单
				WmsMnDto wmsMnDto = null;
				if(!StringUtils.isEmpty(mesCustShipmentDto.getTfSn())) {
					wmsMnDto = sqlQueryUtils.get(wmnSql, new Object[] {mesCustShipmentDto.getTfSn()},  WmsMnDto.class);
				}else {
					wmsMnDto = sqlQueryUtils.get(wmnSql, new Object[] {mesCustShipmentDto.getMainSn()},  WmsMnDto.class);
				}
				if(wmsMnDto == null) {
					wmsMnDto = sqlQueryUtils.get(wmnSql, new Object[] {mesCustShipmentDto.getMBoxCode()},  WmsMnDto.class);
				}
				
				List<MesCustShipmentFormDt> mateList = sqlQueryUtils.queryAll(mateSql, new Object[] {mesCustShipmentDto.getId()}, MesCustShipmentFormDt.class);
				List<BsCustShipmentForm> bsCustShipmentForms = CustShipmentTransUtils.toBsCustShipmentFormList(mesCustShipmentDto,
						mateList, wmsMnDto);
				data.addAll(bsCustShipmentForms);
				JqGridResponse<BsCustShipmentForm> jqGridResponse = new JqGridResponse<BsCustShipmentForm>();
				jqGridResponse.setData(data);
				jqGridResponse.setPageNo(1);
				jqGridResponse.setTotalPage(1);
				jqGridResponse.setTotalSize(data.size());
				return jqGridResponse;
			}
		}
		
		sn = getSnAllLb(sn);
		String sql = "SELECT  B.LB_ID, B.LB_ID AS FIRST_LB_ID, B.NEW_LB_ID AS M_BOX_CODE, "
				+ "B.WP_PEDATE AS FINISH_DATE, G.TB_DATE,"
				+ "O.MO_ID, O.MO_CODE, O.ERP_MO, O.PROD_ID, M.MTRL_CODE AS PROD_CODE, O.MO_TYPE,"
				+ "M.MTRL_NAME AS PROD_NAME, M.MTRL_DESC AS PROD_DESC "
				+ "FROM MES_MO_LB B "
				+ "INNER JOIN BS_MO O ON O.MO_ID = B.MO_ID "
				+ "LEFT JOIN BS_MTRL M ON M.MTRL_ID = O.PROD_ID "
				+ "LEFT JOIN BS_MO_PROG G ON G.MO_ID = O.MO_ID "
				+ "WHERE B.LB_ID = ? AND B.IS_WP_CMP = 'Y' ORDER BY B.CREATED_DATE DESC";
		List<BsCustShipmentForm> queryAll = sqlQueryUtils.queryAll(sql, new Object[] {sn}, BsCustShipmentForm.class);
		if(queryAll != null && queryAll.size() > 0) {
			
			//查询对应箱号有无出货单号
			BsCustShipmentForm bsCustShipmentForm = queryAll.get(0);
			String mBoxCode = bsCustShipmentForm.getmBoxCode();
			if(!StringUtils.isEmpty(mBoxCode)) {
				List<BsCustShipmentForm> queryAll2 = sqlQueryUtils.queryAll(wmnSql, new Object[] {mBoxCode}, BsCustShipmentForm.class);
				if(queryAll2 != null && queryAll2.size() > 0) {
					BsCustShipmentForm pmnForm = queryAll2.get(0);
					String pmnCode = pmnForm.getPmnCode();
					Date shipDate = pmnForm.getShipDate();
					Date addMonths = DateUtils.addMonths(shipDate, 12);
					bsCustShipmentForm.setWarrantyTime("12个月");
					bsCustShipmentForm.setWarrantyUpTime(addMonths);
					bsCustShipmentForm.setPmnCode(pmnCode);
					bsCustShipmentForm.setShipDate(shipDate);
				}
				
			}
		}
		
		return queryByErpMo(queryAll);
	}

	public String getSnAllLb(String sn) {
		//一直往后查直到最后一个数量为1的标签
		String transLbSql = "SELECT MO_ID,LB_ID FROM MES_MO_LB WHERE SRC_LB_ID = ? AND INIT_LB_QTY = 1 ORDER BY CREATED_DATE DESC";
		MesMoLbDto dto = sqlQueryUtils.get(transLbSql, new Object[] {sn}, MesMoLbDto.class);
		if(dto != null && !StringUtils.isEmpty(dto.getLbId())) {
			return getSnAllLb(dto.getLbId());
		}else {
			String accLbSql = "SELECT MO_ID,LB_ID FROM MES_MO_LB_ACCLB WHERE ACCLB_ID = ? ORDER BY CREATED_DATE DESC";
			dto = sqlQueryUtils.get(accLbSql, new Object[] {sn}, MesMoLbDto.class);
			if(dto != null && !StringUtils.isEmpty(dto.getLbId())) {
				return getSnAllLb(dto.getLbId());
			}
			return sn;
		}
	}

	public JqGridResponse<BsCustShipmentForm> queryByMoId(String moId){
		String sql = "SELECT B.LB_ID, B.LB_ID AS FIRST_LB_ID, B.WP_PEDATE AS FINISH_DATE, G.TB_DATE,"
				+ "O.MO_ID, O.MO_CODE, O.ERP_MO, O.PROD_ID, M.MTRL_CODE AS PROD_CODE, O.MO_TYPE, "
				+ "M.MTRL_NAME AS PROD_NAME, M.MTRL_DESC AS PROD_DESC " 
				+ "FROM BS_MO O "
				+ "INNER JOIN BS_MTRL M ON M.MTRL_ID = O.PROD_ID " 
				+ "INNER JOIN MES_MO_LB B ON B.MO_ID = O.MO_ID "
				+ "LEFT JOIN BS_MO_PROG G ON G.MO_ID = O.MO_ID "
				+ "WHERE B.IS_TF_LB = 'N' AND B.WP_RS = 1 AND B.IS_WP_CMP = 'Y' ";
			sql += "AND O.MO_ID = ? ";
	 
		apiLogger.debug("-------查询erp工单已完工标签开始--------------");
		List<BsCustShipmentForm> data =	sqlQueryUtils.queryAll(sql, new Object[] {moId}, BsCustShipmentForm.class);
		apiLogger.debug("-------查询erp工单;moCode已完工标签结束--------------");
		return queryByErpMo(data);
	}

	/**
	 ** 通过工单号查询非转换，合格的标签
	 * 
	 * @param erpMo
	 * @param tbDate
	 * @param moCode 
	 * @param request
	 */
	private JqGridResponse<BsCustShipmentForm> queryByErpMo(List<BsCustShipmentForm> data) {
		
		JqGridResponse<BsCustShipmentForm> jqGridResponse = new JqGridResponse<BsCustShipmentForm>();
		if (data != null && data.size() > 0) {
			BsCustShipmentForm bsmoForm = data.get(0);
			
			
			// 获取当前工单的产品类型
			apiLogger.debug("-------查询erp工单获取当前工单的产品类型开始--------------");
			String ageProdType = checkProdType(bsmoForm.getProdId(),
					bsmoForm.getProdCode() + "[" + bsmoForm.getProdName() + "]");
			logger.info("\n 客户出货ageProdType={};data={}",ageProdType,data);
			apiLogger.debug("-------查询erp工单获取当前工单的产品类型开始2--------------");
			data.get(0).setAgeProdType(ageProdType);
			// 取是否有包装转换标签, 设置SN类型
			apiLogger.debug("-------查询erp工单取是否有包装转换标签, 设置SN类型开始--------------");
			data = judgeProdForLb(ageProdType, data);
			apiLogger.debug("-------查询erp工单取是否有包装转换标签, 设置SN类型开始2--------------");
			// 查询称重信息
			apiLogger.debug("-------查询erp工单查询称重信息开始--------------");
			queryWeightData(ageProdType, data);
			apiLogger.debug("-------查询erp工单查询称重信息开始2--------------");
			// 查询老化信息
			apiLogger.debug("-------查询erp工单查询老化信息开始--------------");
			queryDevData(ageProdType, data);
			apiLogger.debug("-------查询erp工单查询老化信息开始2--------------");
			
			// 查询IPC信息
			apiLogger.debug("-------查询erp工单查询IPC信息开始--------------");
			queryIpcData(ageProdType, data);
			apiLogger.debug("-------查询erp工单查询IPC信息开始2--------------");
			 
			
			// 查询附属标签
			apiLogger.debug("-------查询erp工单查询附属标签开始--------------");
			queryAccLb(data);
			apiLogger.debug("-------查询erp工单查询附属标签开始2--------------");
			
			apiLogger.debug("-------查询erp工单整理附属标签开始--------------");
			//chai
			List<List<BsCustShipmentForm>> subList = subList(data, 100);
			List<BsCustShipmentForm> result = new ArrayList<BsCustShipmentForm>(data.size());
			int threadNum = subList.size();
	        ExecutorService eService = null;
	        try {
	        	if(threadNum==1){
	        		result = judgeAccLbInfo(data, ageProdType, bsmoForm.getMoId());
	        	}else if(threadNum> 1){
	        		if (threadNum > 10) {
						threadNum = 30;
					}
	        		eService = new ThreadPoolExecutor(threadNum, threadNum, 1L, TimeUnit.MILLISECONDS, new LinkedBlockingDeque<Runnable>());
	        		List<Callable<List<BsCustShipmentForm>>> cList = new ArrayList<>();
	        		
	        		for (List<BsCustShipmentForm> dataList : subList) {
            			Callable<List<BsCustShipmentForm>> task = new Callable<List<BsCustShipmentForm>>() {
    						@Override
    						public List<BsCustShipmentForm> call() throws Exception {
    							List<BsCustShipmentForm> tempDataList = judgeAccLbInfo(dataList, ageProdType, bsmoForm.getMoId());
    							return tempDataList;
    						}
    					};
    					cList.add(task);
    				}
	        		Map<Integer, List<BsCustShipmentForm>> dataMap = new HashMap<>();
	        		List<Future<List<BsCustShipmentForm>>> invokeAll = eService.invokeAll(cList);
	        		List<Integer> sort = new ArrayList<>();
	        		for (Future<List<BsCustShipmentForm>> future : invokeAll) {
						List<BsCustShipmentForm> list = future.get();
						dataMap.put(list.get(0).getGroup(), list);
						sort.add(list.get(0).getGroup());
					}
	        		Collections.sort(sort);
	        		for (Integer integer : sort) {
						result.addAll(dataMap.get(integer));
					}
	        	}
	        }catch (Exception e) {
				e.printStackTrace();
				throw new BaseException("查询出错" + e.getMessage());
			}finally {
				if(eService != null) {
					eService.shutdown();
				}
			}
			
			//List<BsCustShipmentForm> result = judgeAccLbInfo(data, ageProdType, bsCustShipmentForm.getMoId());
			
			apiLogger.debug("-------查询erp工单整理附属标签开始2--------------");
			jqGridResponse.setPageNo(1);
			jqGridResponse.setTotalPage(1);
			jqGridResponse.setData(result);
			jqGridResponse.setTotalSize(result.size());
			return jqGridResponse;
		} else {
			jqGridResponse.setPageNo(0);
			jqGridResponse.setTotalPage(0);
			jqGridResponse.setTotalSize(0);
			return jqGridResponse;
		}
	}

	public void queryWeightData(String ageProdType, List<BsCustShipmentForm> data) {
		int i = 1;
		for (BsCustShipmentForm bsCustShipmentForm : data) {
			bsCustShipmentForm.setGroup(i);// 设置分组，方便合并单元格
			String firstLbId = bsCustShipmentForm.getFirstLbId();
			String moId = bsCustShipmentForm.getMoId();
			BsCustShipmentForm weigth = null;
			 
			Object[] params = new Object[] { firstLbId, moId};
			weigth = sqlQueryUtils.get(WEIGHT_SQL_WITHDATE, params, BsCustShipmentForm.class);
			if (weigth != null && weigth.getGrossWeight() != null) {
				bsCustShipmentForm.setNetWeight(weigth.getNetWeight());
				bsCustShipmentForm.setGrossWeight(weigth.getGrossWeight());
				bsCustShipmentForm.setMunit(weigth.getMunit());
			}
			i++;
		}
	}

	public void queryIpcData(String ageProdType, List<BsCustShipmentForm> data) {
		if ("3".equals(ageProdType)) {
			for (BsCustShipmentForm bsCustShipmentForm : data) {
				String mainSn = bsCustShipmentForm.getLbId();
				List<String> lbList = new ArrayList<>();
				lbList.add(mainSn);
				List<String> allTransLb = mesMoLbService.allTransLb(mainSn, bsCustShipmentForm.getMoId(), lbList,0);
				allTransLb = allTransLb.stream().distinct().collect(Collectors.toList());
				HashMap<String, String> map = new HashMap<>();
				for (String lbId : allTransLb) {
					getIpvDev(lbId, bsCustShipmentForm.getMoId(), map);
					// 查询老化信息
					getAgingDevData(map, lbId, bsCustShipmentForm.getMoId());
					
				}
				//获取所有测试数据
				List<Object> allLbIds = new ArrayList<>();
				mesMoLbService.allTransLbNoMoId(mainSn, allLbIds, 0);
				if(allLbIds!=null && !allLbIds.isEmpty()) {
					logger.info("IPC获取标签【"+JSONObject.toJSONString(allLbIds)+"】的所有老化数据");
					getIpvDevNoMoId(allLbIds,map);
					getAgingDevDataNoMoId(map, allLbIds);
				}
				
				if(StringUtils.isBlank(map.get("mac")) || StringUtils.isBlank(map.get("macAddress")) ||
						 StringUtils.isBlank(map.get("chipNumber"))
						|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
						 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
					// 重工工单， 查询初始标签的数据是否存于跨工单中
					String firstLb = allTransLb.get(allTransLb.size() - 1);
					Condition condition = new Condition(MesMoLbDto.class);
					Criteria criteria = condition.createCriteria();
					criteria.andEqualTo("lbId", firstLb);
					criteria.andNotEqualTo("moId", bsCustShipmentForm.getMoId());
					List<MesMoLbDto> otherMoLbDtos = mesMoLbService.selectByCondition(condition);
					if (otherMoLbDtos != null && otherMoLbDtos.size() > 0) {
						for (MesMoLbDto mesMoLbDto2 : otherMoLbDtos) {
							if (!StringUtils.isBlank(mesMoLbDto2.getSrcLbId())) {
								List<String> lbList2 = new ArrayList<>();
								lbList2.add(firstLb);
								List<String> allTransLb2 = mesMoLbService.allTransLb(mesMoLbDto2.getLbId(), mesMoLbDto2.getMoId(), lbList2,0);
								allTransLb2 = allTransLb2.stream().distinct().collect(Collectors.toList());
								for (String srcLb : allTransLb2) {
									getIpvDev(srcLb, mesMoLbDto2.getMoId(), map);
									// 查询老化信息
									getAgingDevData(map, srcLb, mesMoLbDto2.getMoId());
								}
								
								//整改前又有整改
								if(StringUtils.isBlank(map.get("mac")) || StringUtils.isBlank(map.get("macAddress")) ||
										StringUtils.isBlank(map.get("chipNumber"))
										|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
										 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
									// 重工工单， 查询初始标签的数据是否存于跨工单中
									String firstLb2 = allTransLb2.get(allTransLb2.size() - 1);
									Condition condition2 = new Condition(MesMoLbDto.class);
									Criteria criteria2 = condition2.createCriteria();
									criteria2.andEqualTo("lbId", firstLb2);
									criteria2.andNotEqualTo("moId", mesMoLbDto2.getMoId());
									List<MesMoLbDto> otherMoLbDtos2 = mesMoLbService.selectByCondition(condition2);
									if (otherMoLbDtos2 != null && otherMoLbDtos2.size() > 0) {
										for (MesMoLbDto mesMoLbDto3 : otherMoLbDtos2) {
											if (!StringUtils.isBlank(mesMoLbDto3.getSrcLbId())) {
												List<String> lbList3 = new ArrayList<>();
												lbList3.add(firstLb2);
												List<String> allTransLb3 = mesMoLbService.allTransLb(mesMoLbDto3.getLbId(), mesMoLbDto3.getMoId(), lbList3,0);
												allTransLb3 = allTransLb3.stream().distinct().collect(Collectors.toList());
												for (String srcLb : allTransLb3) {
													getIpvDev(srcLb, mesMoLbDto3.getMoId(), map);
													// 查询老化信息
													getAgingDevData(map, srcLb, mesMoLbDto3.getMoId());
												}
												if(StringUtils.isBlank(map.get("mac")) || StringUtils.isBlank(map.get("macAddress")) ||
														StringUtils.isBlank(map.get("chipNumber"))
														|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
														 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
													//查询下是不是在老MES系统里的重工前标签
													queryOldMesSn(allTransLb3.get(allTransLb3.size()-1), map);
												}
											}
										}
									}else {
										//查询下是不是在老MES系统里的重工前标签
										queryOldMesSn(firstLb2, map);
									}
								}
							}
						}
					}else {
						//查询下是不是在老MES系统里的重工前标签
						queryOldMesSn(firstLb, map);
					}
				}
				if(!StringUtils.isBlank(map.get("mac"))){
					bsCustShipmentForm.setMac(map.get("mac"));
				}else if(!StringUtils.isBlank(map.get("macAddress"))){
					bsCustShipmentForm.setMac(map.get("macAddress"));
				}
				if(!StringUtils.isBlank(map.get("serialNum"))) {
					bsCustShipmentForm.setMainChipNo(map.get("serialNum")); 
				}else if(!StringUtils.isBlank(map.get("chipNumber"))){
					bsCustShipmentForm.setMainChipNo(map.get("chipNumber"));
				}
				bsCustShipmentForm.setWifiMac(map.get("wifiMac"));
				bsCustShipmentForm.setImei(map.get("chatImei"));
				bsCustShipmentForm.setPhoneNumber(map.get("phoneNumber"));
				bsCustShipmentForm.setTerminalNo(map.get("terminalNo"));
				
				bsCustShipmentForm.setTerGpsSignal(map.getOrDefault("terGpsSignal",""));
				bsCustShipmentForm.setTerGpsSatellliteNumber(map.getOrDefault("terGpsSatellliteNumber",""));
				bsCustShipmentForm.setTerGpsSnr(map.getOrDefault("terGpsSnr",""));
				bsCustShipmentForm.setTerIccid(map.getOrDefault("iccid",""));
			}
		} 
	}
	
	
	final static String OTHER_ACCLB = "SELECT B.ACCLB_ID, B.ACCLBTYPE_ID, B.LB_ID, S.MTRL_ID, M.MTRL_CODE , M.MTRL_NAME "
			+ "FROM MES_MO_LB_ACCLB B "
			+ "LEFT JOIN BS_MO_PROD_LB_SUB S ON S.MO_ID = B.MO_ID AND S.SUB_LBTYPE_ID = B.ACCLBTYPE_ID "
			+ "LEFT JOIN BS_MTRL M ON M.MTRL_ID = S.MTRL_ID "
			+ "WHERE B.LB_ID = ? AND B.MO_ID <> ? ";

	/**
	 ** 查询附属标签
	 * 
	 * @param data
	 * @param endDate 
	 * @param beginDate 
	 */
	public void queryAccLb(List<BsCustShipmentForm> data) {
		boolean flag = false;
		for (BsCustShipmentForm bsCustShipmentForm : data) {
			if (StringUtils.isEmpty(bsCustShipmentForm.getFirstLbId())) {
				bsCustShipmentForm.setFirstLbId(bsCustShipmentForm.getLbId());
			}
			Object[] sqlParams = new Object[] { bsCustShipmentForm.getFirstLbId(), bsCustShipmentForm.getMoId() };

			List<MesMoLbAcclbForm> mesMoLbAcclbForms = sqlQueryUtils.queryAll(ACCLB_SQL, sqlParams,
					MesMoLbAcclbForm.class);
			if (bsCustShipmentForm.getMesMoLbAcclbForms() == null
					|| bsCustShipmentForm.getMesMoLbAcclbForms().size() == 0) {
				bsCustShipmentForm.setMesMoLbAcclbForms(mesMoLbAcclbForms);
			} else {
				bsCustShipmentForm.getMesMoLbAcclbForms().addAll(mesMoLbAcclbForms);
			}

			BsCustShipmentForm tfLbForm = sqlQueryUtils.get(TF_SQL, sqlParams, BsCustShipmentForm.class);
			if (tfLbForm != null && !StringUtils.isEmpty(tfLbForm.getFirstLbId())) {
				bsCustShipmentForm.setFirstLbId(tfLbForm.getFirstLbId());
				flag = true;
			}else {
				//查询当前标签，在另一工单是否有附属标签
				mesMoLbAcclbForms = sqlQueryUtils.queryAll(OTHER_ACCLB, new Object[] {bsCustShipmentForm.getFirstLbId(), bsCustShipmentForm.getMoId()}, MesMoLbAcclbForm.class);
				if (bsCustShipmentForm.getMesMoLbAcclbForms() == null
						|| bsCustShipmentForm.getMesMoLbAcclbForms().size() == 0) {
					bsCustShipmentForm.setMesMoLbAcclbForms(mesMoLbAcclbForms);
				} else {
					bsCustShipmentForm.getMesMoLbAcclbForms().addAll(mesMoLbAcclbForms);
				}
			}
			
		}
		if (flag) {
			queryAccLb(data);
		}
	}

	/**
	 ** 查询转换前标签src_lb_id->sn_lb_id->new_lb_id
	 * 
	 * @param ageProdType
	 * @param data
	 */
	public List<BsCustShipmentForm> judgeProdForLb(String ageProdType, List<BsCustShipmentForm> data) {
		List<BsCustShipmentForm> newData = new ArrayList<>();
		String sql = "SELECT B.LB_ID, N.PMN_CODE, N.SHIP_DATE "
				+ "FROM MES_MO_LB B "
				+ "LEFT JOIN WMS_CUST_SHIPMENT W ON W.TF_SN = B.LB_ID "
				+ "LEFT JOIN WMS_MN N ON N.PMN_CODE = W.PMN_CODE "
				+ "WHERE B.MO_ID = ? AND B.NEW_LB_ID = ? " ;
		
		BigDecimal queryAgeTime = queryAgeTime(data.get(0));
		for (BsCustShipmentForm bsCustShipmentForm : data) {
			//查询老化小时数
			bsCustShipmentForm.setAgeTime(queryAgeTime);
			
			String lbId = bsCustShipmentForm.getLbId();
			String moId = bsCustShipmentForm.getMoId();
			//查询标签对应的出货单号，出货时间
			if (StringUtils.isBlank(bsCustShipmentForm.getPmnCode())) {
				String sqlPmnString =  "SELECT WM.PMN_CODE, WM.SHIP_DATE "
						+ "FROM WMS_MN WM "
						+ "inner JOIN WMS_CUST_SHIPMENT WCS ON WCS.PMN_CODE = WM.PMN_CODE "
						+ "WHERE WCS.TF_SN = ? OR WCS.M_BOX_CODE = ? ";
				List<WmsMnDto> queryAll = sqlQueryUtils.queryAll(sqlPmnString, new Object[] {lbId, lbId}, WmsMnDto.class);
				if(queryAll != null && queryAll.size() > 0) {
					WmsMnDto wmsMnDto = queryAll.get(0);
					Date shipDate = wmsMnDto.getShipDate();
					String pmnCode = wmsMnDto.getPmnCode();
					bsCustShipmentForm.setShipDate(shipDate);
					bsCustShipmentForm.setPmnCode(pmnCode);
					Date addMonths = DateUtils.addMonths(shipDate, 12);
					bsCustShipmentForm.setWarrantyTime("12个月");
					bsCustShipmentForm.setWarrantyUpTime(addMonths);
				}
			}
			
			bsCustShipmentForm.setAgeProdType(ageProdType);
			// 查询是否有转换前标签, 以及转换前标签的前标签
			List<BsCustShipmentForm> bsCustShipmentForms = sqlQueryUtils.queryAll(sql, new Object[] { moId, lbId}, BsCustShipmentForm.class);
			if (bsCustShipmentForms.size() > 1) {
				// 多转一则重组数组
				for (BsCustShipmentForm bsCustShipmentDto : bsCustShipmentForms) {
					BsCustShipmentForm bsCustShipmentForm2 = new BsCustShipmentForm();
					CopyUtils.copyPropertiesNotNull(bsCustShipmentForm, bsCustShipmentForm2);
					setSn(ageProdType, bsCustShipmentForm2, bsCustShipmentDto.getLbId());
					bsCustShipmentForm2.setFirstLbId(bsCustShipmentDto.getLbId());
					bsCustShipmentForm2.setmBoxCode(lbId);
					bsCustShipmentForm2.setLbId(bsCustShipmentDto.getLbId());
					if(!StringUtils.isEmpty(bsCustShipmentDto.getPmnCode()) && StringUtils.isEmpty(bsCustShipmentForm2.getPmnCode())) {
						bsCustShipmentForm2.setPmnCode(bsCustShipmentDto.getPmnCode());
						bsCustShipmentForm2.setShipDate(bsCustShipmentDto.getShipDate());
						Date addMonths = DateUtils.addMonths(bsCustShipmentDto.getShipDate(), 12);
						bsCustShipmentForm2.setWarrantyTime("12个月");
						bsCustShipmentForm2.setWarrantyUpTime(addMonths);
					}
					newData.add(bsCustShipmentForm2);
				}
			} else if (bsCustShipmentForms.size() == 1 ) {
				BsCustShipmentForm pmnDto = bsCustShipmentForms.get(0);
				// 一转一则使用初始标签作为SN
				if(lbId.contains("*")) {
					setSn(ageProdType, bsCustShipmentForm, pmnDto.getLbId());
				}else {
					setSn(ageProdType, bsCustShipmentForm, lbId);
				}
				bsCustShipmentForm.setFirstLbId(lbId);
				bsCustShipmentForm.setLbId(lbId);
				bsCustShipmentForm.setmBoxCode(lbId);
				if(!StringUtils.isEmpty(pmnDto.getPmnCode()) && StringUtils.isEmpty(bsCustShipmentForm.getPmnCode())) {
					bsCustShipmentForm.setPmnCode(pmnDto.getPmnCode());
					bsCustShipmentForm.setShipDate(pmnDto.getShipDate());
					Date addMonths = DateUtils.addMonths(pmnDto.getShipDate(), 12);
					bsCustShipmentForm.setWarrantyTime("12个月");
					bsCustShipmentForm.setWarrantyUpTime(addMonths);
				}
				newData.add(bsCustShipmentForm);
			}else {
				setSn(ageProdType, bsCustShipmentForm, lbId);
				bsCustShipmentForm.setFirstLbId(lbId);
				bsCustShipmentForm.setLbId(lbId);
				//bsCustShipmentForm.setmBoxCode("");
				newData.add(bsCustShipmentForm);
			}
		}
		if (newData.size() > 0) {
			return newData;
		}
		return data;
	}

	/**
	 ** 设置SN
	 * 
	 * @param ageProdType
	 * @param bsCustShipmentForm
	 * @param lbId
	 */
	public void setSn(String ageProdType, BsCustShipmentForm bsCustShipmentForm, String lbId) {
		switch (ageProdType) {
		case "1": // 整机
			bsCustShipmentForm.setMainSn(lbId);
			break;
		case "2":// 整件
			 bsCustShipmentForm.setMainSn(lbId);
			break;
		case "3":// IPC
			bsCustShipmentForm.setMainSn(lbId);
			break;
		case "4":// 套装
			bsCustShipmentForm.setTfSn(lbId);
			break;
		default:
			break;
		}
	}

	/**
	 * *查询产品类型
	 * 
	 * @param prodId
	 */
	private String checkProdType(String prodId, String prodName) {
		String SQL = "SELECT AGE_PROD_TYPE FROM MES_PROD_EXCH_CONFIG WHERE PROD_ID = ? AND MO_ID IS NULL ";
		JSONObject jsonObject = sqlQueryUtils.get(SQL, new Object[] { prodId }, JSONObject.class);
		if (jsonObject == null || jsonObject.get("ageProdType") == null) {
			checkUtils.throwException("该工单产品[" + prodName + "]未维护产品类型，请在产品交互配置表中维护对应产品类型", new BaseException());
		}
		return (String) jsonObject.get("ageProdType");
	}

	/**
	 * *整理附属标签信息
	 * 
	 * @param data
	 * @param ageProdType 套装的附属标签，作为整机或IPC的SN
	 * @param endDate 
	 * @return
	 */
	public List<BsCustShipmentForm> judgeAccLbInfo(List<BsCustShipmentForm> data, String ageProdType, String moId) {
		List<BsCustShipmentForm> newData = new ArrayList<>();
		String sql = "SELECT B.LB_ID, G.AGE_PROD_TYPE, B.MO_ID,  O.MO_TYPE " 
				+ "FROM MES_MO_LB B "
				+ "LEFT JOIN BS_MO O ON O.MO_ID = B.MO_ID "
				+ "LEFT JOIN MES_PROD_EXCH_CONFIG G ON G.PROD_ID = O.PROD_ID AND G.MO_ID IS NULL "
				+ "WHERE  B.LB_ID = ? AND B.MO_ID <> ?  ";
		if ("4".equals(ageProdType)) {
			// 套装， 需要通过附属标签，筛选出整机和IPC，再查一次设备数据，附属标签数据
			for (BsCustShipmentForm bsCustShipmentForm : data) {
				List<MesMoLbAcclbForm> mesMoLbAcclbForms = bsCustShipmentForm.getMesMoLbAcclbForms();
				if (mesMoLbAcclbForms != null && mesMoLbAcclbForms.size() > 0) {
					int i = 1;
					List<BsCustShipmentForm> tempData = new ArrayList<>(10);
					for (MesMoLbAcclbForm mesMoLbAcclbForm : mesMoLbAcclbForms) {
						// 重组数据， 一个SN对一个附属信息
						BsCustShipmentForm bsCustShipmentForm2 = new BsCustShipmentForm();
						CopyUtils.copyPropertiesNotNull(bsCustShipmentForm, bsCustShipmentForm2);
						if(i != 1) {
							bsCustShipmentForm2.setTfSn(null);
							bsCustShipmentForm2.setMainSn(null);
							bsCustShipmentForm2.setMainChipNo(null);
							bsCustShipmentForm2.setMac(null);
							bsCustShipmentForm2.setWifiMac(null);
							bsCustShipmentForm2.setImei(null);
							bsCustShipmentForm2.setPhoneNumber(null);
							bsCustShipmentForm2.setTerminalNo(null);
						}
						bsCustShipmentForm2.setMtrlName(mesMoLbAcclbForm.getMtrlName());
						bsCustShipmentForm2.setMtrlSn(mesMoLbAcclbForm.getAcclbId());
						bsCustShipmentForm2.setIndex(i + "");
						bsCustShipmentForm2.setMtrlCode(mesMoLbAcclbForm.getMtrlCode());
						tempData.add(bsCustShipmentForm2);
						// 查询附属标签是否存在于其他工单，是IPC或者是整机
						String acclbId = mesMoLbAcclbForm.getAcclbId();
						apiLogger.info("查询附属标签是否存在于其他工单，是IPC或者是整机");
						BsCustShipmentForm mesMoLbForm = sqlQueryUtils.get(sql,new Object[] { acclbId, bsCustShipmentForm2.getMoId() }, BsCustShipmentForm.class);
						apiLogger.info("查询附属标签是否存在于其他工单，是IPC或者是整机2");
						if (mesMoLbForm != null) {
							String acclbProdType = mesMoLbForm.getAgeProdType();
							if (!StringUtils.isBlank(acclbProdType)) {
								switch (acclbProdType) {
								case "1":// 整机
									bsCustShipmentForm2.setMesMoLbAcclbForms(null);
									bsCustShipmentForm2.setFirstLbId(acclbId);
									// 查询是否还需要获取设备数据
									if(StringUtils.isBlank(bsCustShipmentForm2.getMainSn())){
										bsCustShipmentForm2.setMainSn(acclbId);
										logger.info("======》查询附属标签测试数据："+acclbId);
										queryAcclbInOhterMoDev(bsCustShipmentForm2, mesMoLbForm.getMoId());
									}
									
									// 继续查询对应附属标签在其他工单下的附属标签
									List<MesMoLbAcclbForm> queryAcclbInOtherMo = queryAcclbInOtherMo(acclbId,
											mesMoLbForm.getMoId());
									if (queryAcclbInOtherMo != null && queryAcclbInOtherMo.size() > 0) {
										int j = 1;
										for (MesMoLbAcclbForm mesMoLbAcclbForm2 : queryAcclbInOtherMo) {
											// 扩展重组
											BsCustShipmentForm bsCustShipmentForm3 = new BsCustShipmentForm();
											CopyUtils.copyPropertiesNotNull(bsCustShipmentForm2, bsCustShipmentForm3);
											bsCustShipmentForm3.setMainSn(null);
											bsCustShipmentForm3.setMainChipNo(null);
											bsCustShipmentForm3.setMac(null);
											bsCustShipmentForm3.setWifiMac(null);
											bsCustShipmentForm3.setImei(null);
											bsCustShipmentForm3.setPhoneNumber(null);
											bsCustShipmentForm3.setTerminalNo(null);
											
											bsCustShipmentForm3.setMtrlName(mesMoLbAcclbForm2.getMtrlName());
											bsCustShipmentForm3.setMtrlSn(mesMoLbAcclbForm2.getAcclbId());
											bsCustShipmentForm3.setMtrlCode(mesMoLbAcclbForm2.getMtrlCode());
											bsCustShipmentForm3.setIndex(i + "." + j);
											j++;
											tempData.add(bsCustShipmentForm3);
										}
									}
									break;

								case "3":// IPC
									bsCustShipmentForm2.setMesMoLbAcclbForms(null);
									bsCustShipmentForm2.setFirstLbId(acclbId);
									
									// 查询是否还需要获取ipc数据
									if(StringUtils.isBlank(bsCustShipmentForm2.getMainSn())){
										bsCustShipmentForm2.setMainSn(acclbId);
										queryAcclbInOhterMoIpcDev(bsCustShipmentForm2, mesMoLbForm.getMoId());
									}
									 
									// 继续查询对应附属标签在其他工单下的附属标签
									List<MesMoLbAcclbForm> acclbIpc = queryAcclbInOtherMo(acclbId,	mesMoLbForm.getMoId());
									if (acclbIpc != null && acclbIpc.size() > 0) {
										int j = 1;
										for (MesMoLbAcclbForm mesMoLbAcclbForm2 : acclbIpc) {
											// 扩展重组
											BsCustShipmentForm bsCustShipmentForm3 = new BsCustShipmentForm();
											CopyUtils.copyPropertiesNotNull(bsCustShipmentForm2, bsCustShipmentForm3);
											bsCustShipmentForm3.setMainSn(null);
											bsCustShipmentForm3.setMainChipNo(null);
											bsCustShipmentForm3.setMac(null);
											bsCustShipmentForm3.setWifiMac(null);
											bsCustShipmentForm3.setImei(null);
											bsCustShipmentForm3.setPhoneNumber(null);
											bsCustShipmentForm3.setTerminalNo(null);
											bsCustShipmentForm3.setMtrlCode(mesMoLbAcclbForm2.getMtrlCode());
											bsCustShipmentForm3.setMtrlName(mesMoLbAcclbForm2.getMtrlName());
											bsCustShipmentForm3.setMtrlSn(mesMoLbAcclbForm2.getAcclbId());
											bsCustShipmentForm3.setIndex(i + "." + j);
											j++;
											tempData.add(bsCustShipmentForm3);
										}
									}
									break;
								default:
									// 普通物料，插入
									// 继续查询对应附属标签在其他工单下的附属标签
									List<MesMoLbAcclbForm> queryAcclbInOtherMo1 = queryAcclbInOtherMo(acclbId, mesMoLbForm.getMoId());
									if (queryAcclbInOtherMo1 != null && queryAcclbInOtherMo1.size() > 0) {
										int j = 1;
										for (MesMoLbAcclbForm mesMoLbAcclbForm2 : queryAcclbInOtherMo1) {
											// 扩展重组
											BsCustShipmentForm bsCustShipmentForm3 = new BsCustShipmentForm();
											CopyUtils.copyPropertiesNotNull(bsCustShipmentForm2, bsCustShipmentForm3);
											bsCustShipmentForm3.setMainSn(null);
											bsCustShipmentForm3.setMainChipNo(null);
											bsCustShipmentForm3.setMac(null);
											bsCustShipmentForm3.setWifiMac(null);
											bsCustShipmentForm3.setImei(null);
											bsCustShipmentForm3.setPhoneNumber(null);
											bsCustShipmentForm3.setTerminalNo(null);
											
											bsCustShipmentForm3.setMtrlCode(mesMoLbAcclbForm2.getMtrlCode());
											bsCustShipmentForm3.setMtrlName(mesMoLbAcclbForm2.getMtrlName());
											bsCustShipmentForm3.setMtrlSn(mesMoLbAcclbForm2.getAcclbId());
											bsCustShipmentForm3.setIndex(i + "." + j);
											j++;
											tempData.add(bsCustShipmentForm3);
										}
									}
									break;
								}
							} else {
								// 继续查询对应附属标签在其他工单下的附属标签
								List<MesMoLbAcclbForm> queryAcclbInOtherMo1 = queryAcclbInOtherMo(acclbId, mesMoLbForm.getMoId() );
								if (queryAcclbInOtherMo1 != null && queryAcclbInOtherMo1.size() > 0) {
									int j = 1;
									for (MesMoLbAcclbForm mesMoLbAcclbForm2 : queryAcclbInOtherMo1) {
										// 扩展重组
										BsCustShipmentForm bsCustShipmentForm3 = new BsCustShipmentForm();
										CopyUtils.copyPropertiesNotNull(bsCustShipmentForm2, bsCustShipmentForm3);
										bsCustShipmentForm3.setMainSn(null);
										bsCustShipmentForm3.setMainChipNo(null);
										bsCustShipmentForm3.setMac(null);
										bsCustShipmentForm3.setWifiMac(null);
										bsCustShipmentForm3.setImei(null);
										bsCustShipmentForm3.setPhoneNumber(null);
										bsCustShipmentForm3.setTerminalNo(null);
										bsCustShipmentForm3.setMtrlCode(mesMoLbAcclbForm2.getMtrlCode());
										bsCustShipmentForm3.setMtrlName(mesMoLbAcclbForm2.getMtrlName());
										bsCustShipmentForm3.setMtrlSn(mesMoLbAcclbForm2.getAcclbId());
										bsCustShipmentForm3.setIndex(i + "." + j);
										j++;
										tempData.add(bsCustShipmentForm3);
									}
								}
							}
						}
						i++;
					}
					newData.addAll(tempData);
				} else {
					newData.add(bsCustShipmentForm);
				}
			}
		} else {
			// 不是套装产品， 把附属标签list扩展，重组数据
			for (BsCustShipmentForm bsCustShipmentForm : data) {
				List<MesMoLbAcclbForm> mesMoLbAcclbForms = bsCustShipmentForm.getMesMoLbAcclbForms();
				if (mesMoLbAcclbForms != null && mesMoLbAcclbForms.size() > 0) {
					int i = 1;
					for (MesMoLbAcclbForm mesMoLbAcclbForm : mesMoLbAcclbForms) {
						BsCustShipmentForm bsCustShipmentForm2 = new BsCustShipmentForm();
						CopyUtils.copyPropertiesNotNull(bsCustShipmentForm, bsCustShipmentForm2);
						if(i != 1) {
							bsCustShipmentForm2.setMainSn(null);
							bsCustShipmentForm2.setMainChipNo(null);
							bsCustShipmentForm2.setMac(null);
							bsCustShipmentForm2.setWifiMac(null);
							bsCustShipmentForm2.setImei(null);
							bsCustShipmentForm2.setPhoneNumber(null);
							bsCustShipmentForm2.setTerminalNo(null);
						}
						bsCustShipmentForm2.setMtrlCode(mesMoLbAcclbForm.getMtrlCode());
						bsCustShipmentForm2.setMtrlName(mesMoLbAcclbForm.getMtrlName());
						bsCustShipmentForm2.setMtrlSn(mesMoLbAcclbForm.getAcclbId());
						bsCustShipmentForm2.setIndex(i + "");
						newData.add(bsCustShipmentForm2);
						// 查询附属标签是否存在于其他工单
						String acclbId = mesMoLbAcclbForm.getAcclbId();
						BsCustShipmentForm mesMoLbForm = sqlQueryUtils.get(sql,
								new Object[] { acclbId, bsCustShipmentForm2.getMoId() }, BsCustShipmentForm.class);
						if (mesMoLbForm != null) {
							// 继续查询对应附属标签在其他工单下的附属标签
							List<MesMoLbAcclbForm> acclb = queryAcclbInOtherMo(acclbId, mesMoLbForm.getMoId());
							if (acclb != null && acclb.size() > 0) {
								int j = 1;
								for (MesMoLbAcclbForm mesMoLbAcclbForm2 : acclb) {
									// 扩展重组
									BsCustShipmentForm bsCustShipmentForm3 = new BsCustShipmentForm();
									CopyUtils.copyPropertiesNotNull(bsCustShipmentForm2, bsCustShipmentForm3);
									
									bsCustShipmentForm3.setMainSn(null);
									bsCustShipmentForm3.setMainChipNo(null);
									bsCustShipmentForm3.setMac(null);
									bsCustShipmentForm3.setWifiMac(null);
									bsCustShipmentForm3.setImei(null);
									bsCustShipmentForm3.setPhoneNumber(null);
									bsCustShipmentForm3.setTerminalNo(null);
									
									bsCustShipmentForm3.setMtrlCode(mesMoLbAcclbForm2.getMtrlCode());
									bsCustShipmentForm3.setMtrlName(mesMoLbAcclbForm2.getMtrlName());
									bsCustShipmentForm3.setMtrlSn(mesMoLbAcclbForm2.getAcclbId());
									bsCustShipmentForm3.setIndex(i + "." + j);
									j++;
									newData.add(bsCustShipmentForm3);
								}
							}
						}
						i++;
					}
				} else {
					newData.add(bsCustShipmentForm);
				}

			}
		}
		return newData;
	}

	public void queryAcclbInOhterMoIpcDev(BsCustShipmentForm bsCustShipmentForm, String moId) {
		// 判断是否有IPC数据，有则不查
		String mainSn = bsCustShipmentForm.getMainSn();
		List<String> lbList = new ArrayList<>();
		lbList.add(mainSn);
		List<String> allTransLb = mesMoLbService.allTransLb(mainSn, moId, lbList,0);
		allTransLb = allTransLb.stream().distinct().collect(Collectors.toList());
		HashMap<String, String> map = new HashMap<>();
		
		
		for (String lbId : allTransLb) {
			logger.info("===>标签值："+lbId);
			// 查询IPC信息
			getIpvDev(lbId, moId, map);
			// 查询老化信息
			getAgingDevData(map, lbId, moId);
		}
		logger.info("mac1:"+map.get("macAddress"));
		//获取所有测试数据
		List<Object> allLbIds = new ArrayList<>();
		mesMoLbService.allTransLbNoMoId(mainSn, allLbIds, 0);
		if(allLbIds!=null && !allLbIds.isEmpty()) {
			getIpvDevNoMoId(allLbIds,map);
			getAgingDevDataNoMoId(map, allLbIds);
		}
		logger.info("mac2:"+map.get("macAddress"));
		if(StringUtils.isBlank(map.get("mac")) || StringUtils.isBlank(map.get("macAddress")) ||
				 StringUtils.isBlank(map.get("chipNumber"))
				|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
				 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
			// 重工工单， 查询初始标签的数据是否存于跨工单中
			String firstLb = allTransLb.get(allTransLb.size() - 1);
			Condition condition = new Condition(MesMoLbDto.class);
			Criteria criteria = condition.createCriteria();
			criteria.andEqualTo("lbId", firstLb);
			criteria.andNotEqualTo("moId", bsCustShipmentForm.getMoId());
			List<MesMoLbDto> otherMoLbDtos = mesMoLbService.selectByCondition(condition);
			if (otherMoLbDtos != null && otherMoLbDtos.size() > 0) {
				for (MesMoLbDto mesMoLbDto2 : otherMoLbDtos) {
					if (!StringUtils.isBlank(mesMoLbDto2.getSrcLbId())) {
						List<String> lbList2 = new ArrayList<>();
						lbList2.add(firstLb);
						List<String> allTransLb2 = mesMoLbService.allTransLb(mesMoLbDto2.getLbId(), mesMoLbDto2.getMoId(), lbList2,0);
						allTransLb2 = allTransLb2.stream().distinct().collect(Collectors.toList());
						for (String srcLb : allTransLb2) {
							getIpvDev(srcLb, mesMoLbDto2.getMoId(), map);
							// 查询老化信息
							getAgingDevData(map, srcLb, mesMoLbDto2.getMoId());
						}
						
						//整改前又有整改
						if(StringUtils.isBlank(map.get("mac")) || StringUtils.isBlank(map.get("macAddress")) ||
								 StringUtils.isBlank(map.get("chipNumber"))
									|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
									 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
							// 重工工单， 查询初始标签的数据是否存于跨工单中
							String firstLb2 = allTransLb2.get(allTransLb2.size() - 1);
							Condition condition2 = new Condition(MesMoLbDto.class);
							Criteria criteria2 = condition2.createCriteria();
							criteria2.andEqualTo("lbId", firstLb2);
							criteria2.andNotEqualTo("moId", mesMoLbDto2.getMoId());
							List<MesMoLbDto> otherMoLbDtos2 = mesMoLbService.selectByCondition(condition2);
							if (otherMoLbDtos2 != null && otherMoLbDtos2.size() > 0) {
								for (MesMoLbDto mesMoLbDto3 : otherMoLbDtos2) {
									if (!StringUtils.isBlank(mesMoLbDto3.getSrcLbId())) {
										List<String> lbList3 = new ArrayList<>();
										lbList3.add(firstLb2);
										List<String> allTransLb3 = mesMoLbService.allTransLb(mesMoLbDto3.getLbId(), mesMoLbDto3.getMoId(), lbList3,0);
										allTransLb3 = allTransLb3.stream().distinct().collect(Collectors.toList());
										for (String srcLb : allTransLb3) {
											getIpvDev(srcLb, mesMoLbDto3.getMoId(), map);
											// 查询老化信息
											getAgingDevData(map, srcLb, mesMoLbDto3.getMoId());
										}
										if(StringUtils.isBlank(map.get("mac")) || StringUtils.isBlank(map.get("macAddress")) ||
												 StringUtils.isBlank(map.get("chipNumber"))
													|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
													 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
											//查询下是不是在老MES系统里的重工前标签
											queryOldMesSn(lbList3.get(lbList3.size()-1), map);
										}
									}
								}
							}else {
								//查询下是不是在老MES系统里的重工前标签
								queryOldMesSn(firstLb2, map);
							}
						}
						
					}
				}
			}else {
				//查询下是不是在老MES系统里的重工前标签
				queryOldMesSn(firstLb, map);
			}
		}
		logger.info("mac3:"+map.get("macAddress")+"==="+map.get("mac"));
		if(!StringUtils.isBlank(map.get("mac"))){
			bsCustShipmentForm.setMac(map.get("mac"));
		}else if(!StringUtils.isBlank(map.get("macAddress"))){
			bsCustShipmentForm.setMac(map.get("macAddress"));
		}
		if(!StringUtils.isBlank(map.get("serialNum"))) {
			bsCustShipmentForm.setMainChipNo(map.get("serialNum")); 
		}else if(!StringUtils.isBlank(map.get("chipNumber"))){
			bsCustShipmentForm.setMainChipNo(map.get("chipNumber"));
		}
		bsCustShipmentForm.setWifiMac(map.get("wifiMac"));
		bsCustShipmentForm.setImei(map.get("chatImei"));
		bsCustShipmentForm.setPhoneNumber(map.get("phoneNumber"));
		bsCustShipmentForm.setTerminalNo(map.get("terminalNo"));
		
		bsCustShipmentForm.setTerGpsSignal(map.getOrDefault("terGpsSignal",""));
		bsCustShipmentForm.setTerGpsSatellliteNumber(map.getOrDefault("terGpsSatellliteNumber",""));
		bsCustShipmentForm.setTerGpsSnr(map.getOrDefault("terGpsSnr",""));
		bsCustShipmentForm.setTerIccid(map.getOrDefault("iccid", ""));
		
	}

	private void getIpvDevNoMoId(List<Object> lbIds,Map<String, String> map) {
		Condition ipcCondition = new Condition(MesIpcLogDto.class);
		Criteria ipcCriteria = ipcCondition.createCriteria();
		ipcCriteria.andIn("lbId", lbIds);
		ipcCriteria.andEqualTo("result", "Y");
		ipcCondition.setOrderByClause("createdDate desc");
		List<MesIpcLogDto> ipcList = mesIpcLogService.selectByCondition(ipcCondition);
		if (ipcList != null && ipcList.size() > 0) {
			MesIpcLogDto mesIpcLogDto = ipcList.get(0);
			mesMoLbService.getagingInfo(map, null, null, null, mesIpcLogDto,mesIpcLogDto.getLbId());
		}
	}


	private void getIpvDev(String lbId, String moId, Map<String, String> map) {
		Condition ipcCondition = new Condition(MesIpcLogDto.class);
		Criteria ipcCriteria = ipcCondition.createCriteria();
		ipcCriteria.andEqualTo("lbId", lbId);
		ipcCriteria.andEqualTo("moId", moId);
		ipcCriteria.andEqualTo("result", "Y");
		ipcCondition.setOrderByClause("createdDate desc");
		List<MesIpcLogDto> ipcList = mesIpcLogService.selectByCondition(ipcCondition);
		if (ipcList != null && ipcList.size() > 0) {
			MesIpcLogDto mesIpcLogDto = ipcList.get(0);
			mesMoLbService.getagingInfo(map, null, null, null, mesIpcLogDto,lbId);
		}
	}
	
	

	/**
	 ** 查询附属标签整机老化数据
	 * 
	 * @param bsCustShipmentForm
	 * @param moId               附属工单
	 */
	public void queryAcclbInOhterMoDev(BsCustShipmentForm bsCustShipmentForm, String moId) {
		String mainSn = bsCustShipmentForm.getMainSn();
		List<String> lbList = new ArrayList<>();
		lbList.add(mainSn);
		List<String> allTransLb = mesMoLbService.allTransLb(mainSn, moId, lbList,0);
		allTransLb = allTransLb.stream().distinct().collect(Collectors.toList());
		HashMap<String, String> map = new HashMap<>();
		for (String lbId : allTransLb) {
			logger.info("======》查询附属标签详细测试日志："+lbId);
			// 查询老化前后数据
			getAgingDevData(map, lbId, moId);
		}
		//获取所有测试数据
		List<Object> allLbIds = new ArrayList<>();
		mesMoLbService.allTransLbNoMoId(mainSn, allLbIds, 0);
		if(allLbIds!=null && !allLbIds.isEmpty()) {
			getAgingDevDataNoMoId(map, allLbIds);
		}
		
		if(StringUtils.isBlank(map.get("mac")) ||  StringUtils.isBlank(map.get("chipNumber"))
				|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
				 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
			// 重工工单， 查询初始标签的数据是否存于跨工单中
			String firstLb = allTransLb.get(allTransLb.size() - 1);
			Condition condition = new Condition(MesMoLbDto.class);
			Criteria criteria = condition.createCriteria();
			criteria.andEqualTo("lbId", firstLb);
			criteria.andNotEqualTo("moId", bsCustShipmentForm.getMoId());
			List<MesMoLbDto> otherMoLbDtos = mesMoLbService.selectByCondition(condition);
			if (otherMoLbDtos != null && otherMoLbDtos.size() > 0) {
				for (MesMoLbDto mesMoLbDto2 : otherMoLbDtos) {
					if (!StringUtils.isBlank(mesMoLbDto2.getSrcLbId())) {
						List<String> lbList2 = new ArrayList<>();
						lbList2.add(firstLb);
						List<String> allTransLb2 = mesMoLbService.allTransLb(mesMoLbDto2.getLbId(), mesMoLbDto2.getMoId(), lbList2,0);
						allTransLb2 = allTransLb2.stream().distinct().collect(Collectors.toList());
						for (String srcLb : allTransLb2) {
							getAgingDevData(map, srcLb, mesMoLbDto2.getMoId());
						}
						//整改前又有整改
						if(StringUtils.isBlank(map.get("mac")) ||  StringUtils.isBlank(map.get("chipNumber"))
								|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
								 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
							// 重工工单， 查询初始标签的数据是否存于跨工单中
							String firstLb2 = allTransLb2.get(allTransLb2.size() - 1);
							Condition condition2 = new Condition(MesMoLbDto.class);
							Criteria criteria2 = condition2.createCriteria();
							criteria2.andEqualTo("lbId", firstLb2);
							criteria2.andNotEqualTo("moId", mesMoLbDto2.getMoId());
							List<MesMoLbDto> otherMoLbDtos2 = mesMoLbService.selectByCondition(condition2);
							if (otherMoLbDtos2 != null && otherMoLbDtos2.size() > 0) {
								for (MesMoLbDto mesMoLbDto3 : otherMoLbDtos2) {
									if (!StringUtils.isBlank(mesMoLbDto3.getSrcLbId())) {
										List<String> lbList3 = new ArrayList<>();
										lbList3.add(firstLb2);
										List<String> allTransLb3 = mesMoLbService.allTransLb(mesMoLbDto3.getLbId(), mesMoLbDto3.getMoId(), lbList3,0);
										allTransLb3 = allTransLb3.stream().distinct().collect(Collectors.toList());
										for (String srcLb : allTransLb3) {
											getAgingDevData(map, srcLb, mesMoLbDto3.getMoId());
										}
										if(StringUtils.isBlank(map.get("mac")) ||  StringUtils.isBlank(map.get("chipNumber"))
											|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
											 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
											queryOldMesSn(lbList3.get(lbList3.size()-1), map);
										}
									}
								}
							}else {
								queryOldMesSn(firstLb2, map);
							}
						}
						
					}
				}
			}else {
				queryOldMesSn(firstLb, map);
			}
		}
		
		
		bsCustShipmentForm.setMainChipNo(map.getOrDefault("chipNumber", ""));
		bsCustShipmentForm.setMac(map.getOrDefault("macAddress", ""));
		bsCustShipmentForm.setWifiMac(map.getOrDefault("wifiMac", ""));
		bsCustShipmentForm.setImei(map.getOrDefault("chatImei", ""));
		bsCustShipmentForm.setPhoneNumber(map.getOrDefault("phoneNumber", ""));
		bsCustShipmentForm.setTerminalNo(map.getOrDefault("terminalNo", ""));
		
		bsCustShipmentForm.setTerGpsSignal(map.getOrDefault("terGpsSignal",""));
		bsCustShipmentForm.setTerGpsSatellliteNumber(map.getOrDefault("terGpsSatellliteNumber",""));
		bsCustShipmentForm.setTerGpsSnr(map.getOrDefault("terGpsSnr",""));
		bsCustShipmentForm.setTerIccid(map.getOrDefault("iccid", ""));
	}

	private void getAgingDevData(Map<String, String> map, String lbId, String moId) {
		Condition condition = new Condition(MesAgingAfterInfoDto.class);
		Criteria criteria = condition.createCriteria();
		criteria.andEqualTo("lbId", lbId);
		criteria.andEqualTo("moId", moId);
		criteria.andEqualTo("isPass", "Y");
		condition.setOrderByClause("createdDate desc");
		List<MesAgingAfterInfoDto> list = agingAfterInfoService.selectByCondition(condition);
		if (list.size() > 0) {
			MesAgingAfterInfoDto infoDto = list.get(0);
			mesMoLbService.getagingInfo(map, infoDto, null, null, null,lbId);
		}
		Condition condition2 = new Condition(MesAgeDevBeforeDto.class);
		Criteria criteria2 = condition2.createCriteria();
		criteria2.andEqualTo("beforeBarcode", lbId);
		criteria2.andEqualTo("moId", moId);
		criteria2.andEqualTo("validFlag", "1");
		criteria2.andEqualTo("beforeResult", "1");
		condition2.setOrderByClause("createdDate desc");
		List<MesAgeDevBeforeDto> list2 = ageDevBeforeService.selectByCondition(condition2);
		if (list2.size() == 1) {
			MesAgeDevBeforeDto beforeDto = list2.get(0);
			//查询老化前过站日志是否有设置过无效,如果还是有效可以使用，否则不给使用。
			Condition condition3 = new Condition(MesAgeDevBeforePassDto.class);
			Criteria criteria3 = condition3.createCriteria();
			criteria3.andEqualTo("beforeBarcode", lbId);
			criteria3.andEqualTo("moId", moId);
			condition3.setOrderByClause("createdDate desc");
			List<MesAgeDevBeforePassDto> list3 = ageDevBeforePassService.selectByCondition(condition3);
			if (list3.size()>0) {
				MesAgeDevBeforePassDto dtoNew = list3.get(0);
				if("1".equals(dtoNew.getValidFlag())) {
					mesMoLbService.getagingInfo(map, null, beforeDto, null, null,lbId);
				}
			}
		}else if(list2.size() >=2) {
			for (MesAgeDevBeforeDto beforeDto : list2) {
				//查询老化前过站日志是否有设置过无效,如果还是有效可以使用，否则不给使用。
				Condition condition3 = new Condition(MesAgeDevBeforePassDto.class);
				Criteria criteria3 = condition3.createCriteria();
				criteria3.andEqualTo("beforeBarcode", lbId);
				criteria3.andEqualTo("moId", moId);
				condition3.setOrderByClause("createdDate desc");
				List<MesAgeDevBeforePassDto> list3 = ageDevBeforePassService.selectByCondition(condition3);
				if (list3.size()>0) {
					MesAgeDevBeforePassDto dtoNew = list3.get(0);
					if("1".equals(dtoNew.getValidFlag())) {
						mesMoLbService.getagingInfo(map, null, beforeDto, null, null,lbId);
					}
				}
			}
		}
	}
	
	public  void getLastNewNotNull(Map<String, String> map,int i,List<MesAgingAfterInfoDto> list) {
		MesAgingAfterInfoDto agingAfterInfoDto = list.get(i);
		logger.info("获取标签【"+agingAfterInfoDto.getLbId()+"】第【"+i+"】次数据");
		 i++;
		 if (StringUtils.isBlank(map.get("terminalNo"))) {
             map.put("terminalNo", agingAfterInfoDto.getTerminalNo());
         }
         if (StringUtils.isBlank(map.get("cardModel")) && agingAfterInfoDto.getCardModel() != null) {
             map.put("cardModel", agingAfterInfoDto.getCardModel().toString());
         }
         if (StringUtils.isBlank(map.get("blueToothVersion"))) {
             map.put("blueToothVersion", agingAfterInfoDto.getBlueToothVersion());
         }
         if (StringUtils.isBlank(map.get("terminalId"))) {
             map.put("terminalId", agingAfterInfoDto.getTerminalId());
         }
         if (StringUtils.isBlank(map.get("sn"))) {
             map.put("sn", agingAfterInfoDto.getSn());
         }
         if (StringUtils.isBlank(map.get("phoneNumber"))) {
             map.put("phoneNumber", agingAfterInfoDto.getPhoneNumber());
         }
         if (StringUtils.isBlank(map.get("chipNumber"))) {
             map.put("chipNumber", agingAfterInfoDto.getChipNumber());
         }
         if (StringUtils.isBlank(map.get("machineType"))) {
             map.put("machineType", agingAfterInfoDto.getMachineType());
         }
         if (StringUtils.isBlank(map.get("gpsSoftVersion"))) {
             map.put("gpsSoftVersion", agingAfterInfoDto.getGpsSoftVersion());
         }
         if (StringUtils.isBlank(map.get("temperatureValue"))) {
             map.put("temperatureValue", agingAfterInfoDto.getTemperatureValue());
         }
         if (StringUtils.isBlank(map.get("gpsIp"))) {
             map.put("gpsIp", agingAfterInfoDto.getGpsIp());
         }
         if (StringUtils.isBlank(map.get("chatVersion"))) {
             map.put("chatVersion", agingAfterInfoDto.getChatVersion());
         }
         if (StringUtils.isBlank(map.get("chatIp"))) {
             map.put("chatIp", agingAfterInfoDto.getChatIp());
         }
         if (StringUtils.isBlank(map.get("chatImei"))) {
             map.put("chatImei", agingAfterInfoDto.getChatImei());
         }
         if (StringUtils.isBlank(map.get("macAddress"))) {
             map.put("macAddress", agingAfterInfoDto.getMacAddress());
         }
         if (StringUtils.isBlank(map.get("cardVersion"))) {
             map.put("cardVersion", agingAfterInfoDto.getCardVersion());
         }
         if (StringUtils.isBlank(map.get("ipcVersion"))) {
             map.put("ipcVersion", agingAfterInfoDto.getIpcVersion());
         }
         if (StringUtils.isBlank(map.get("scmVersion"))) {
             map.put("scmVersion", agingAfterInfoDto.getScmVersion());
         }
         if (StringUtils.isBlank(map.get("softVersion"))) {
             map.put("softVersion", agingAfterInfoDto.getSoftVersion());
         }
         if (StringUtils.isBlank(map.get("machineDate"))) {
             map.put("machineDate", agingAfterInfoDto.getMachineDate());
         }
         if (StringUtils.isBlank(map.get("wifiMac"))) {
         	//查询根据IP获取MAC地址数据,能查到放入老化测试数据中
				StreamaxIpMacDto dto = new StreamaxIpMacDto();
				dto.setLbId(agingAfterInfoDto.getLbId());
			    dto = streamaxIpMacService.selectOne(dto);
				if(dto!=null) {
					Date d1= agingAfterInfoDto.getCreatedDate();
					Date d2 = dto.getUpdateDate();
					//使用wifimac的最新测试结果
					if(d1!=null) {
						if(d1.before(d2)||agingAfterInfoDto.getWifiMac()==null) {
							map.put("wifiMac", dto.getBeforeMac());
						}else {
							map.put("wifiMac", agingAfterInfoDto.getWifiMac());
						}
					}else {
						map.put("wifiMac", dto.getBeforeMac());
					}
				}else {
					map.put("wifiMac", agingAfterInfoDto.getWifiMac());
				}
         }
         if (StringUtils.isBlank(map.get("wifiIp"))) {
             map.put("wifiIp", agingAfterInfoDto.getWifiIp());
         }
         if (StringUtils.isBlank(map.get("timeArea")) && agingAfterInfoDto.getTimeArea() != null) {
             map.put("timeArea", agingAfterInfoDto.getTimeArea().toString());
         }
         if(i==list.size()) {//集合循环完了退出
        	 return;
         }
         getLastNewNotNull(map,i,list);
	}
	
	

	/**
	 * 直接根据所有可能标签按时间获取最新的测试日志
	 * @param map
	 * @param lbId
	 */
	private void getAgingDevDataNoMoId(Map<String, String> map, List<Object> lbIds) {
		Condition condition = new Condition(MesAgingAfterInfoDto.class);
		condition.setOrderByClause("createdDate desc");
		Criteria criteria = condition.createCriteria();
		criteria.andIn("lbId", lbIds);
		criteria.andEqualTo("isPass", "Y");
		List<MesAgingAfterInfoDto> list = agingAfterInfoService.selectByCondition(condition);
		if (list.size() > 0&&list.size()==1) {
			MesAgingAfterInfoDto infoDto = list.get(0);
			mesMoLbService.getagingInfo(map, infoDto, null, null, null,list.get(0).getLbId());
		}else if(list.size() > 0&&list.size()>1){//如果最新有多条，优先获取最新一条记录的值。全部获取完
			getLastNewNotNull(map, 0,list); 
		}
		Condition condition2 = new Condition(MesAgeDevBeforeDto.class);
		Criteria criteria2 = condition2.createCriteria();
		criteria2.andIn("beforeBarcode", lbIds);
		criteria2.andEqualTo("validFlag", "1");
		criteria2.andEqualTo("beforeResult", "1");
		condition2.setOrderByClause("createdDate desc");
		List<MesAgeDevBeforeDto> list2 = ageDevBeforeService.selectByCondition(condition2);
		if (list2.size() ==1) {
			MesAgeDevBeforeDto beforeDto = list2.get(0);
			//查询老化前过站日志是否有设置过无效,如果还是有效可以使用，否则不给使用。
			Condition condition3 = new Condition(MesAgeDevBeforePassDto.class);
			Criteria criteria3 = condition3.createCriteria();
			criteria3.andEqualTo("beforeBarcode", beforeDto.getBeforeBarcode());
			condition3.setOrderByClause("createdDate desc");
			List<MesAgeDevBeforePassDto> list3 = ageDevBeforePassService.selectByCondition(condition3);
			if (list3.size() > 0) {
				MesAgeDevBeforePassDto dtoNew = list3.get(0);
				if("1".equals(dtoNew.getValidFlag())) {
					mesMoLbService.getagingInfo(map, null, beforeDto, null, null,beforeDto.getBeforeBarcode());
				}
			}
		}else if(list2.size() >=2){
		    for (MesAgeDevBeforeDto beforeDto : list2) {
                //查询老化前过站日志是否有设置过无效,如果还是有效可以使用，否则不给使用。
                Condition condition3 = new Condition(MesAgeDevBeforePassDto.class);
                Criteria criteria3 = condition3.createCriteria();
                criteria3.andEqualTo("beforeBarcode", beforeDto.getBeforeBarcode());
                condition3.setOrderByClause("createdDate desc");
                List<MesAgeDevBeforePassDto> list3 = ageDevBeforePassService.selectByCondition(condition3);
                if (list3.size()>0) {
                    MesAgeDevBeforePassDto dtoNew = list3.get(0);
                    if("1".equals(dtoNew.getValidFlag())) {
                        mesMoLbService.getagingInfo(map, null, beforeDto, null, null,beforeDto.getBeforeBarcode());
                    }
                }
            }
		}
	}
	

	/**
	 * .查询附属标签在另外工单下的附属标签
	 * 
	 * @param acclbId
	 * @param moId
	 * @return
	 */
	public List<MesMoLbAcclbForm> queryAcclbInOtherMo(String acclbId, String moId) {
		Object[] sqlParams = new Object[] { acclbId, moId };
		List<MesMoLbAcclbForm> queryAll = sqlQueryUtils.queryAll(ACCLB_SQL, sqlParams, MesMoLbAcclbForm.class);
		return queryAll;
	}

	/**
	 ** 通过标签查询标签的老化信息
	 * 
	 * @param ageProdType
	 * 
	 * @param data
	 */
	public void queryDevData(String ageProdType, List<BsCustShipmentForm> data) {
		if (ageProdType.equals("1") || ageProdType.equals("2")) {
			for (BsCustShipmentForm bsCustShipmentForm : data) {
				String mainSn = bsCustShipmentForm.getLbId();
				List<String> lbList = new ArrayList<>();
				lbList.add(mainSn);
				//只能查到本工单的
				List<String> allTransLb = mesMoLbService.allTransLb(mainSn, bsCustShipmentForm.getMoId(), lbList,0);
				allTransLb = allTransLb.stream().distinct().collect(Collectors.toList());
				HashMap<String, String> map = new HashMap<>();
				for (String lbId : allTransLb) {
					// 查询老化前后数据
					getAgingDevData(map, lbId, bsCustShipmentForm.getMoId());
				}
		            
				//获取所有测试数据
				List<Object> allLbIds = new ArrayList<>();
				mesMoLbService.allTransLbNoMoId(mainSn, allLbIds, 0);
				if(allLbIds!=null && !allLbIds.isEmpty()) {
					logger.info("整机整件获取标签【"+JSONObject.toJSONString(allLbIds)+"】的所有老化数据");
					getAgingDevDataNoMoId(map, allLbIds);
				}
				
				if(StringUtils.isBlank(map.get("macAddress")) || StringUtils.isBlank(map.get("chipNumber"))
						|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
						 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))
						) {
					// 重工工单， 查询初始标签的数据是否存于跨工单中
					String firstLb = allTransLb.get(allTransLb.size() - 1);
					Condition condition = new Condition(MesMoLbDto.class);
					Criteria criteria = condition.createCriteria();
					criteria.andEqualTo("lbId", firstLb);
					criteria.andNotEqualTo("moId", bsCustShipmentForm.getMoId());
					List<MesMoLbDto> otherMoLbDtos = mesMoLbService.selectByCondition(condition);
					if (otherMoLbDtos != null && otherMoLbDtos.size() > 0) {
						for (MesMoLbDto mesMoLbDto2 : otherMoLbDtos) {
							if (!StringUtils.isBlank(mesMoLbDto2.getSrcLbId())) {
								List<String> lbList2 = new ArrayList<>();
								lbList2.add(firstLb);
								List<String> allTransLb2 = mesMoLbService.allTransLb(mesMoLbDto2.getLbId(), mesMoLbDto2.getMoId(), lbList2,0);
								allTransLb2 = allTransLb2.stream().distinct().collect(Collectors.toList());
								for (String srcLb : allTransLb2) {
									getAgingDevData(map, srcLb, mesMoLbDto2.getMoId());
								}
								//整改前又有整改
								if(StringUtils.isBlank(map.get("macAddress")) || StringUtils.isBlank(map.get("chipNumber"))
										|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
										 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
									// 重工工单， 查询初始标签的数据是否存于跨工单中
									String firstLb2 = allTransLb2.get(allTransLb2.size() - 1);
									Condition condition2 = new Condition(MesMoLbDto.class);
									Criteria criteria2 = condition2.createCriteria();
									criteria2.andEqualTo("lbId", firstLb2);
									criteria2.andNotEqualTo("moId", mesMoLbDto2.getMoId());
									List<MesMoLbDto> otherMoLbDtos2 = mesMoLbService.selectByCondition(condition2);
									if (otherMoLbDtos2 != null && otherMoLbDtos2.size() > 0) {
										for (MesMoLbDto mesMoLbDto3 : otherMoLbDtos2) {
											if (!StringUtils.isBlank(mesMoLbDto3.getSrcLbId())) {
												List<String> lbList3 = new ArrayList<>();
												lbList3.add(firstLb2);
												List<String> allTransLb3 = mesMoLbService.allTransLb(mesMoLbDto3.getLbId(), mesMoLbDto3.getMoId(), lbList3,0);
												allTransLb3 = allTransLb3.stream().distinct().collect(Collectors.toList());
												for (String srcLb : allTransLb3) {
													getAgingDevData(map, srcLb, mesMoLbDto3.getMoId());
												}
												if(StringUtils.isBlank(map.get("macAddress")) || StringUtils.isBlank(map.get("chipNumber"))
														|| StringUtils.isBlank(map.get("chatImei")) || StringUtils.isBlank(map.get("wifiMac")) 
														 || StringUtils.isBlank(map.get("phoneNumber")) || StringUtils.isBlank(map.get("terminalNo"))) {
													//查询下是不是在老MES系统里的重工前标签
													queryOldMesSn(lbList3.get(lbList3.size()-1), map);
												}
											}
										}
									}else {
										//查询下是不是在老MES系统里的重工前标签
										queryOldMesSn(firstLb2, map);
									}
								}
							}
						}
					}else {
						//查询下是不是在老MES系统里的重工前标签
						queryOldMesSn(firstLb, map);
					}
				}
				bsCustShipmentForm.setMainChipNo(map.get("chipNumber"));
				bsCustShipmentForm.setMac(map.get("macAddress"));
				bsCustShipmentForm.setWifiMac(map.get("wifiMac"));
				bsCustShipmentForm.setImei(map.get("chatImei"));
				bsCustShipmentForm.setPhoneNumber(map.get("phoneNumber"));
				bsCustShipmentForm.setTerminalNo(map.get("terminalNo"));
				
				bsCustShipmentForm.setTerGpsSignal(map.getOrDefault("terGpsSignal",""));
				bsCustShipmentForm.setTerGpsSatellliteNumber(map.getOrDefault("terGpsSatellliteNumber",""));
				bsCustShipmentForm.setTerGpsSnr(map.getOrDefault("terGpsSnr",""));
				bsCustShipmentForm.setTerIccid(map.getOrDefault("iccid", ""));
			}
			 
		}
		return;
	}

	

	public void queryOldMesSn(String firstLb, HashMap<String, String> map) {
		String snSql = BsCustShipmentForPmnCodeService.SN_SQL;
		MesCustShipmentDto mesCustShipmentDto = sqlQueryUtils.get(snSql, new Object[] {firstLb,firstLb}, MesCustShipmentDto.class);
		if(mesCustShipmentDto != null) {
			if(StringUtils.isBlank(map.get("macAddress")) ) {
				map.put("macAddress", mesCustShipmentDto.getMac());
			}
			if(StringUtils.isBlank(map.get("chipNumber"))){
				map.put("chipNumber", mesCustShipmentDto.getMainChipNo());
			}
			if(StringUtils.isBlank(map.get("chatImei"))){
				map.put("chatImei", mesCustShipmentDto.getImei());
			}
			if(StringUtils.isBlank(map.get("wifiMac"))){
				map.put("wifiMac", mesCustShipmentDto.getWifiMac());
			}
			if(StringUtils.isBlank(map.get("phoneNumber"))){
				map.put("phoneNumber", mesCustShipmentDto.getPhoneNumber());
			}
			if( StringUtils.isBlank(map.get("terminalNo"))){
				map.put("phoneNumber", mesCustShipmentDto.getPhoneNumber());
			}
		}
	}

	/**
	 ** 查询标准老化时间
	 * 
	 * @param bsCustShipmentForm
	 * @return
	 */
	public BigDecimal queryAgeTime(BsCustShipmentForm bsCustShipmentForm) {
		String sql = "SELECT AGE_TIME FROM MES_PROD_EXCH_CONFIG WHERE MO_ID = ?";
		String moId = bsCustShipmentForm.getMoId();
		String prodId = bsCustShipmentForm.getProdId();
		BigDecimal bigDecimal = new BigDecimal(2);
		BsCustShipmentForm dbForm = sqlQueryUtils.get(sql, new Object[] { moId }, BsCustShipmentForm.class);
		if (dbForm == null || dbForm.getAgeTime() == null) {
			sql = "SELECT AGE_TIME FROM MES_PROD_EXCH_CONFIG WHERE PROD_ID = ? AND MO_ID IS NULL ";
			dbForm = sqlQueryUtils.get(sql, new Object[] { prodId }, BsCustShipmentForm.class);
		}
		if(dbForm != null && dbForm.getAgeTime() != null) {
			return dbForm.getAgeTime();
		}
		return bigDecimal;
	}

	/**
	 * 分割数组方法
	 * 
	 * @param list
	 * @param len
	 * @return
	 */
	public <T> List<List<T>> subList(List<T> list, int len) {
		List<List<T>> resultList = new ArrayList<>();
		for (int i = 0; i < list.size(); i++) {
			if (i % len == 0) {
				int count = i / len;
				List<T> sublist = list.stream().limit((count + 1) * len).skip(count * len).collect(Collectors.toList());
				resultList.add(sublist);
			}
		}
		return resultList;
	}

	public List<BsCustShipmentForm> search(JqGridRequest<BsCustShipmentForm> gridRequest){
		JqGridResponse<BsCustShipmentForm> jqGridResponse = query(gridRequest);
		List<BsCustShipmentForm> data = new ArrayList<BsCustShipmentForm>();
		if(jqGridResponse != null) {
			data = jqGridResponse.getData();
		}
		if(data != null && data.size() > 0) {
			logger.info("出货报表导出的数据长度:"+data.size());
			for(BsCustShipmentForm bsForm : data) {
				if(StringUtil.isBlank(bsForm.getPmnCode()) && (StringUtils.isNotBlank(bsForm.getTfSn()) || StringUtils.isNotBlank(bsForm.getMainSn()))) {
					String linkSql =  "select DELIVERYBILLNO as pmn_code, to_date(DELIVERYDATE, 'yyyy-mm-dd') as ship_date from APPS.CUX_MES_SHIP_HIS@EBSDBLINK "
							+ "where 1=1 and (";
					if(StringUtils.isNotBlank(bsForm.getTfSn())){
						linkSql += 	"PRODUCTIONSN = '"+bsForm.getTfSn()+"' OR MATERIELSN = '"+bsForm.getTfSn()+"' ";
						if(StringUtils.isNotBlank(bsForm.getMainSn())){
							linkSql += 	" OR PRODUCTIONSN = '"+bsForm.getMainSn()+"' OR MATERIELSN = '"+bsForm.getMainSn()+"' ";
						}
					}else {
						linkSql += 	"PRODUCTIONSN = '"+bsForm.getMainSn()+"' OR MATERIELSN = '"+bsForm.getMainSn()+"' ";
					}
					linkSql += ")";
					List<BsCustShipmentForm> linkSqlList = sqlQueryUtils.queryAll(linkSql, null, BsCustShipmentForm.class);
					if(linkSqlList != null && linkSqlList.size() > 0) {
						bsForm.setPmnCode(linkSqlList.get(0).getPmnCode());
						bsForm.setShipDate(linkSqlList.get(0).getShipDate());
					}
				}
			}
			logger.info("出货报表查询结束");
		}
		return data;
	}
	
	public void exportAction(String tableName, List<BsCustShipmentForm> data, HttpServletResponse response){
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		HSSFSheet sheet = hssfWorkbook.createSheet("sheet");
		tableName = tableName + ".xls";
		if (data == null || data.size() == 0) {
			exportExcel(response, hssfWorkbook, tableName);
			return ;
		}
		//建立表头
		HSSFRow row0 = sheet.createRow(0);
		for (int i = 0; i < 40; i++) {
			sheet.setColumnWidth(i, 7000);
		}
		
		createTableTitle(row0,Locale.SIMPLIFIED_CHINESE);
		
		HSSFCreationHelper helper = hssfWorkbook.getCreationHelper();
		
		HSSFCellStyle dateTimeCellStyle = hssfWorkbook.createCellStyle();
		dateTimeCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		dateTimeCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		dateTimeCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));
		
		HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 遍历一次得出需要合并的行分组坐标
		List<Integer> split = new ArrayList<>();
		for (int i = 0; i < data.size(); i++) { 
			int j = i+1;
			HSSFRow row = sheet.createRow(j);
			BsCustShipmentForm bsCustShipmentForm = data.get(i);
		
			String index = bsCustShipmentForm.getIndex();
			if("1".equals(index) || StringUtils.isEmpty(index)) {
				//起始行
				split.add(j);
			}
			
			setRowValue(bsCustShipmentForm, row, hssfWorkbook,dateTimeCellStyle,cellStyle, dateTimeCellStyle);
		}
		for(int i=0; i<split.size()-1; i++) {
			Integer beginIndex = split.get(i);//开始
			Integer endIndex = split.get(i+1)-1;//结束
			if(beginIndex.compareTo(endIndex) == 0) {
				continue;
			}
			merge(sheet, beginIndex, endIndex, cellStyle);
			
		}
		//最后一段合并
		int size = data.size();
		if(size > 1 && split.size()>1) {
			Integer endRow = split.get(split.size()-1);
			if(endRow.compareTo(size) != 0) {
				merge(sheet, endRow, size, cellStyle);
			}
		}
		exportExcel(response, hssfWorkbook, tableName);
		
	}
	
	public void export(String tableName, JqGridRequest<BsCustShipmentForm> gridRequest, HttpServletResponse response) {
		exportAction(tableName, search(gridRequest), response);
	}

	private void merge(HSSFSheet sheet,int beginIndex,int endIndex, HSSFCellStyle cellStyle) {
		CellRangeAddress cellRangeAddress0 = new CellRangeAddress(beginIndex, endIndex, 0, 0);
		sheet.addMergedRegion(cellRangeAddress0);
		
		for (int i = 1; i < 7; i++) {
			getMergeCellValue(sheet, beginIndex, endIndex, i, cellStyle);
		}
		
		//不是套装的时候合并 SN和测试数据
		CellRangeAddress cellRangeAddress1 = new CellRangeAddress(beginIndex, endIndex, 1, 1);
		sheet.addMergedRegion(cellRangeAddress1);
		
		CellRangeAddress cellRangeAddress2 = new CellRangeAddress(beginIndex, endIndex, 2, 2);
		sheet.addMergedRegion(cellRangeAddress2);
			
		CellRangeAddress cellRangeAddress3 = new CellRangeAddress(beginIndex, endIndex, 3, 3);
		sheet.addMergedRegion(cellRangeAddress3);
		
		CellRangeAddress cellRangeAddress4 = new CellRangeAddress(beginIndex, endIndex, 4, 4);
		sheet.addMergedRegion(cellRangeAddress4);
		
		CellRangeAddress cellRangeAddress5 = new CellRangeAddress(beginIndex, endIndex, 5, 5);
		sheet.addMergedRegion(cellRangeAddress5);
		
		CellRangeAddress cellRangeAddress6 = new CellRangeAddress(beginIndex, endIndex, 6, 6);
		sheet.addMergedRegion(cellRangeAddress6);
		
		CellRangeAddress cellRangeAddress7 = new CellRangeAddress(beginIndex, endIndex, 7, 7);
		sheet.addMergedRegion(cellRangeAddress7);
			
		
		CellRangeAddress cellRangeAddress8 = new CellRangeAddress(beginIndex, endIndex, 8, 8);
		sheet.addMergedRegion(cellRangeAddress8);
		
		CellRangeAddress cellRangeAddress9 = new CellRangeAddress(beginIndex, endIndex, 9, 9);
		sheet.addMergedRegion(cellRangeAddress9);
		
		CellRangeAddress cellRangeAddress10 = new CellRangeAddress(beginIndex, endIndex, 10, 10);
		sheet.addMergedRegion(cellRangeAddress10);
		
		CellRangeAddress cellRangeAddress11 = new CellRangeAddress(beginIndex, endIndex, 11, 11);
		sheet.addMergedRegion(cellRangeAddress11);
		
		CellRangeAddress cellRangeAddress12 = new CellRangeAddress(beginIndex, endIndex, 12, 12);
		sheet.addMergedRegion(cellRangeAddress12);
		
		CellRangeAddress cellRangeAddress13 = new CellRangeAddress(beginIndex, endIndex, 13, 13);
		sheet.addMergedRegion(cellRangeAddress13);
		
		CellRangeAddress cellRangeAddress14 = new CellRangeAddress(beginIndex, endIndex, 14, 14);
		sheet.addMergedRegion(cellRangeAddress14);
		
		CellRangeAddress cellRangeAddress15 = new CellRangeAddress(beginIndex, endIndex, 15, 15);
		sheet.addMergedRegion(cellRangeAddress15);
		
		CellRangeAddress cellRangeAddress16 = new CellRangeAddress(beginIndex, endIndex, 16, 16);
		sheet.addMergedRegion(cellRangeAddress16);
		
		CellRangeAddress cellRangeAddress17 = new CellRangeAddress(beginIndex, endIndex, 17, 17);
		sheet.addMergedRegion(cellRangeAddress17);

		CellRangeAddress cellRangeAddress18 = new CellRangeAddress(beginIndex, endIndex, 18, 18);
		sheet.addMergedRegion(cellRangeAddress18);
		
		CellRangeAddress cellRangeAddress19 = new CellRangeAddress(beginIndex, endIndex, 19, 19);
		sheet.addMergedRegion(cellRangeAddress19);
		
		CellRangeAddress cellRangeAddress20 = new CellRangeAddress(beginIndex, endIndex, 20, 20);
		sheet.addMergedRegion(cellRangeAddress20);
		
		CellRangeAddress cellRangeAddress21 = new CellRangeAddress(beginIndex, endIndex, 21, 21);
		sheet.addMergedRegion(cellRangeAddress21);
		
		CellRangeAddress cellRangeAddress22 = new CellRangeAddress(beginIndex, endIndex, 22, 22);
		sheet.addMergedRegion(cellRangeAddress22);
		
		CellRangeAddress cellRangeAddress23 = new CellRangeAddress(beginIndex, endIndex, 23, 23);
		sheet.addMergedRegion(cellRangeAddress23);
		
		CellRangeAddress cellRangeAddress24 = new CellRangeAddress(beginIndex, endIndex, 24, 24);
		sheet.addMergedRegion(cellRangeAddress24);
//		
//		CellRangeAddress cellRangeAddress25 = new CellRangeAddress(beginIndex, endIndex, 25, 25);
//		sheet.addMergedRegion(cellRangeAddress25);
//		
//		CellRangeAddress cellRangeAddress26 = new CellRangeAddress(beginIndex, endIndex, 26, 26);
//		sheet.addMergedRegion(cellRangeAddress26);
	}
	
	/**
	 ** 将合并单元格的有数据那行放在合并组的第一行
	 * @param sheet
	 * @param beginIndex 第一行
	 * @param endIndex  最后一行
	 * @param i 合并列
	 * @param cellStyle 
	 */
	private void getMergeCellValue(HSSFSheet sheet, int beginIndex, int endIndex, int i, HSSFCellStyle cellStyle) {
		for (int k = beginIndex; k <= endIndex; k++) {
			HSSFCell cell = sheet.getRow(k).getCell(i);
			if(!isCellEmpty(cell) ) {
				if( beginIndex == k) {
					break;
				}
                //不为空时设置进第一行
				HSSFCell srcCell = sheet.getRow(beginIndex).getCell(i);
				switch (cell.getCellTypeEnum()) {
				    case STRING:
						srcCell.setCellValue(cell.getStringCellValue());
						break;
					case NUMERIC:
						srcCell.setCellValue(cell.getNumericCellValue());
						break;
					default:
						srcCell.setCellValue(cell.getStringCellValue());
				     break;
				 }
				 srcCell.setCellStyle(cellStyle);
				 break;
			 }
		}
	}

	public boolean isCellEmpty(HSSFCell cell) {
		if(cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
			return true;
		}
		if(cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue().isEmpty()) {
			return true;
		}
		
		return false;
	}

	private void setRowValue(BsCustShipmentForm bsCustShipmentForm, HSSFRow row, HSSFWorkbook hssfWorkbook, HSSFCellStyle dateTimeCellStyle, HSSFCellStyle cellStyle, HSSFCellStyle dateTimeCellStyle2) {
		
		HSSFCell cell0 = row.createCell(0);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getTfSn())) {
			cell0.setCellStyle(cellStyle);
			cell0.setCellValue(bsCustShipmentForm.getTfSn());
		}
		
		HSSFCell cell1 = row.createCell(1);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMainSn())) {
			cell1.setCellStyle(cellStyle);
			cell1.setCellValue(bsCustShipmentForm.getMainSn());
		}
	
//		HSSFCell cell2 = row.createCell(2);
//		if(!StringUtils.isEmpty(bsCustShipmentForm.getIpcSn())) {
//			cell2.setCellStyle(cellStyle);
//			cell2.setCellValue(bsCustShipmentForm.getIpcSn());
//		}
		
		HSSFCell cell3 = row.createCell(2);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMainChipNo())) {
			cell3.setCellStyle(cellStyle);
			cell3.setCellValue(bsCustShipmentForm.getMainChipNo());
		}
		
//		HSSFCell cell4 = row.createCell(4);
//		if(!StringUtils.isEmpty(bsCustShipmentForm.getIpcChipNo())) {
//			cell4.setCellStyle(cellStyle);
//			cell4.setCellValue(bsCustShipmentForm.getIpcChipNo());
//		}
//		
//		HSSFCell cell30 = row.createCell(5);
//		if(!StringUtils.isEmpty(bsCustShipmentForm.getCameraMac())) {
//			cell30.setCellStyle(cellStyle);
//			cell30.setCellValue(bsCustShipmentForm.getCameraMac());
//		}
		
		
		
		HSSFCell cell5 = row.createCell(3);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMac())) {
			cell5.setCellStyle(cellStyle);
			cell5.setCellValue(bsCustShipmentForm.getMac());
		}
		
		HSSFCell cell6 = row.createCell(4);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getWifiMac())) {
			cell6.setCellStyle(cellStyle);
			cell6.setCellValue(bsCustShipmentForm.getWifiMac());
		}
		
		HSSFCell cell7 = row.createCell(5);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getImei())) {
			cell7.setCellStyle(cellStyle);
			cell7.setCellValue(bsCustShipmentForm.getImei());
		}
		
		HSSFCell cell8 = row.createCell(6);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getPhoneNumber())) {
			cell8.setCellStyle(cellStyle);
			cell8.setCellValue(bsCustShipmentForm.getPhoneNumber());
		}
		
		HSSFCell cell9 = row.createCell(7);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getTerminalNo())) {
			cell9.setCellStyle(cellStyle);
			cell9.setCellValue(bsCustShipmentForm.getTerminalNo());
		}
		
		
		HSSFCell cell10 = row.createCell(8);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getProdCode())) {
			cell10.setCellStyle(cellStyle);
			cell10.setCellValue(bsCustShipmentForm.getProdCode());
		}
			
		HSSFCell cell11 = row.createCell(9);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getProdName())) {
			cell11.setCellStyle(cellStyle);
			cell11.setCellValue(bsCustShipmentForm.getProdName());
		}
		
		HSSFCell cell12 = row.createCell(10);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getProdDesc())) {
			cell12.setCellStyle(cellStyle);
			cell12.setCellValue(bsCustShipmentForm.getProdDesc());
		}
		
		HSSFCell cell13 = row.createCell(11);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getErpMo())) {
			cell13.setCellStyle(cellStyle);
			cell13.setCellValue(bsCustShipmentForm.getErpMo());
		}
		
		HSSFCell cell14 = row.createCell(12);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMoCode())) {
			cell14.setCellStyle(cellStyle);
			cell14.setCellValue(bsCustShipmentForm.getMoCode());
		}
		
		 
		HSSFCell cell15 = row.createCell(13);
		if(bsCustShipmentForm.getTbDate()!= null ) {
			cell15.setCellStyle(dateTimeCellStyle);
			cell15.setCellValue(bsCustShipmentForm.getTbDate());
		}
		
		HSSFCell cell16 = row.createCell(14);
		if(bsCustShipmentForm.getFinishDate()!= null ) {
			cell16.setCellStyle(dateTimeCellStyle);
			cell16.setCellValue(bsCustShipmentForm.getFinishDate());
		}
		
		HSSFCell cell17 = row.createCell(15);
		if(bsCustShipmentForm.getAgeTime() != null) {
			cell17.setCellStyle(cellStyle);
			cell17.setCellValue(bsCustShipmentForm.getAgeTime().doubleValue());
		}

		HSSFCell cell18 = row.createCell(16);
		if(bsCustShipmentForm.getNetWeight() != null) {
			cell18.setCellStyle(cellStyle);
			cell18.setCellValue(bsCustShipmentForm.getNetWeight().doubleValue());
		}
		
		HSSFCell cell19 = row.createCell(17);
		if(bsCustShipmentForm.getGrossWeight() != null) {
			cell19.setCellStyle(cellStyle);
			cell19.setCellValue(bsCustShipmentForm.getGrossWeight().doubleValue());
		}
		
		HSSFCell cell20 = row.createCell(18);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMunit())) {
			cell20.setCellStyle(cellStyle);
			cell20.setCellValue(bsCustShipmentForm.getMunit());
		}
		
		
		HSSFCell cell21 = row.createCell(19);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getmBoxCode())) {
			cell21.setCellStyle(cellStyle);
			cell21.setCellValue(bsCustShipmentForm.getmBoxCode());
		}
		
		
		HSSFCell cell22 = row.createCell(20);
		if(bsCustShipmentForm.getQcTime() != null) {
			cell22.setCellStyle(dateTimeCellStyle);
			cell22.setCellValue(bsCustShipmentForm.getQcTime());		
		}
		
		HSSFCell cell23 = row.createCell(21);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getPmnCode())) {
			cell23.setCellStyle(cellStyle);
			cell23.setCellValue(bsCustShipmentForm.getPmnCode());
		}
	
		
		HSSFCell cell24 = row.createCell(22);
		if(bsCustShipmentForm.getShipDate() != null) {
			cell24.setCellStyle(dateTimeCellStyle2);
			cell24.setCellValue(bsCustShipmentForm.getShipDate());	
		}
		
		HSSFCell cell25 = row.createCell(23);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getWarrantyTime())) {
			cell25.setCellStyle(cellStyle);
			cell25.setCellValue(bsCustShipmentForm.getWarrantyTime());
		}
		
		
		HSSFCell cell26 = row.createCell(24);
		if(bsCustShipmentForm.getWarrantyUpTime() != null) {
			cell26.setCellStyle(dateTimeCellStyle2);
			cell26.setCellValue(bsCustShipmentForm.getWarrantyUpTime());
		}
		
		HSSFCell cell27 = row.createCell(25);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getIndex())) {
			cell27.setCellStyle(cellStyle);
			cell27.setCellValue(bsCustShipmentForm.getIndex());
		}
		
		
		HSSFCell cell28 = row.createCell(26);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMtrlName())) {
			cell28.setCellStyle(cellStyle);
			cell28.setCellValue(bsCustShipmentForm.getMtrlName());
		}
		
		
		HSSFCell cell29 = row.createCell(27);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getMtrlSn())) {
			cell29.setCellStyle(cellStyle);
			cell29.setCellValue(bsCustShipmentForm.getMtrlSn());
		}
		
		HSSFCell cell30 = row.createCell(28);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getTerGpsSignal())) {
			cell30.setCellStyle(cellStyle);
			cell30.setCellValue(bsCustShipmentForm.getTerGpsSignal());
		}
		
		HSSFCell cell31 = row.createCell(29);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getTerGpsSatellliteNumber())) {
			cell31.setCellStyle(cellStyle);
			cell31.setCellValue(bsCustShipmentForm.getTerGpsSatellliteNumber());
		}
		
		HSSFCell cell32 = row.createCell(30);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getTerGpsSnr())) {
			cell32.setCellStyle(cellStyle);
			cell32.setCellValue(bsCustShipmentForm.getTerGpsSnr());
		}
		
		HSSFCell cell33 = row.createCell(31);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getTerIccid())) {
			cell33.setCellStyle(cellStyle);
			cell33.setCellValue(bsCustShipmentForm.getTerIccid());
		}
		
		HSSFCell cell34 = row.createCell(32);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getCusPoCode())) {
			cell34.setCellStyle(cellStyle);
			cell34.setCellValue(bsCustShipmentForm.getCusPoCode());
		}
		
		HSSFCell cell35 = row.createCell(33);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getCusPrdCode())) {
			cell35.setCellStyle(cellStyle);
			cell35.setCellValue(bsCustShipmentForm.getCusPrdCode());
		}
		
		HSSFCell cell36 = row.createCell(34);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getCusPrdName())) {
			cell36.setCellStyle(cellStyle);
			cell36.setCellValue(bsCustShipmentForm.getCusPrdName());
		}
		
		HSSFCell cell37 = row.createCell(35);
		if(!StringUtils.isEmpty(bsCustShipmentForm.getCusPrdSpec())) {
			cell37.setCellStyle(cellStyle);
			cell37.setCellValue(bsCustShipmentForm.getCusPrdSpec());
		}
	}

	private void createTableTitle(HSSFRow row0, Locale locale) {
		HSSFCell cell0 = row0.createCell(0);
		String tfsnMsg = messageSource.getMessage("column.tfsn", locale);
		cell0.setCellValue(tfsnMsg);
		
		HSSFCell cell1 = row0.createCell(1);
		cell1.setCellValue(messageSource.getMessage("column.mainsn", locale));
		
//		HSSFCell cell2 = row0.createCell(2);
//		cell2.setCellValue(messageSource.getMessage("column.camerasn", locale));
		
		HSSFCell cell3 = row0.createCell(2);
		cell3.setCellValue(messageSource.getMessage("column.mainchipno", locale));
		
//		HSSFCell cell4 = row0.createCell(4);
//		cell4.setCellValue(messageSource.getMessage("column.ipcchipno", locale));
//		
//		HSSFCell cell30 = row0.createCell(5);
//		cell30.setCellValue(messageSource.getMessage("column.cameramac", locale));
//		
		
		HSSFCell cell5 = row0.createCell(3);
		cell5.setCellValue(messageSource.getMessage("column.macs", locale));
		
		HSSFCell cell6 = row0.createCell(4);
		cell6.setCellValue(messageSource.getMessage("column.wifimac", locale));
		
		HSSFCell cell7 = row0.createCell(5);
		cell7.setCellValue(messageSource.getMessage("column.imei", locale));
		
		HSSFCell cell8 = row0.createCell(6);
		cell8.setCellValue(messageSource.getMessage("column.phonenumber", locale));

		HSSFCell cell9 = row0.createCell(7);
		cell9.setCellValue(messageSource.getMessage("column.terminalno", locale));
		
		HSSFCell cell10 = row0.createCell(8);
		cell10.setCellValue(messageSource.getMessage("column.prodcode", locale));
		
		HSSFCell cell11 = row0.createCell(9);
		cell11.setCellValue(messageSource.getMessage("column.prodname", locale));

		HSSFCell cell12 = row0.createCell(10);
		cell12.setCellValue(messageSource.getMessage("column.proddesc", locale));
		
		HSSFCell cell13 = row0.createCell(11);
		cell13.setCellValue(messageSource.getMessage("column.erpmo", locale));
		
		HSSFCell cell14 = row0.createCell(12);
		cell14.setCellValue(messageSource.getMessage("column.mocode", locale));
		
		HSSFCell cell15 = row0.createCell(13);
		cell15.setCellValue(messageSource.getMessage("column.tbdate", locale));
		
		HSSFCell cell16 = row0.createCell(14);
		cell16.setCellValue(messageSource.getMessage("column.finishdate", locale));
		
		HSSFCell cell17 = row0.createCell(15);
		cell17.setCellValue(messageSource.getMessage("column.agetime", locale));

		HSSFCell cell18 = row0.createCell(16);
		cell18.setCellValue(messageSource.getMessage("column.netweight", locale));
		
		HSSFCell cell19 = row0.createCell(17);
		cell19.setCellValue(messageSource.getMessage("column.grossweight", locale));
		
		HSSFCell cell20 = row0.createCell(18);
		cell20.setCellValue(messageSource.getMessage("column.munit", locale));
		
		HSSFCell cell21 = row0.createCell(19);
		cell21.setCellValue(messageSource.getMessage("column.mboxcode", locale));
		
		HSSFCell cell22 = row0.createCell(20);
		cell22.setCellValue(messageSource.getMessage("column.qctime", locale));
		
		HSSFCell cell23 = row0.createCell(21);
		cell23.setCellValue(messageSource.getMessage("column.pmncode", locale));
		
		HSSFCell cell24 = row0.createCell(22);
		cell24.setCellValue(messageSource.getMessage("column.shipdate", locale));
		
		HSSFCell cell25 = row0.createCell(23);
		cell25.setCellValue(messageSource.getMessage("column.warrantytime", locale));
		
		HSSFCell cell26 = row0.createCell(24);
		cell26.setCellValue(messageSource.getMessage("column.warrantyuptime", locale));
		
		HSSFCell cell27 = row0.createCell(25);
		cell27.setCellValue(messageSource.getMessage("column.index", locale));
		
		HSSFCell cell28 = row0.createCell(26);
		cell28.setCellValue(messageSource.getMessage("column.mtrlname", locale));
		
		HSSFCell cell29 = row0.createCell(27);
		cell29.setCellValue(messageSource.getMessage("column.mtrlsn", locale));
		
		HSSFCell cell30 = row0.createCell(28);
		cell30.setCellValue(messageSource.getMessage("column.tergpssignal", locale));
		
		HSSFCell cell31 = row0.createCell(29);
		cell31.setCellValue(messageSource.getMessage("column.tergpssatelllitenumber", locale));
		
		HSSFCell cell32 = row0.createCell(30);
		cell32.setCellValue(messageSource.getMessage("column.tersnr", locale));
		
		HSSFCell cell33 = row0.createCell(31);
		cell33.setCellValue(messageSource.getMessage("column.terIccid", locale));
		
		HSSFCell cell34 = row0.createCell(32);
		cell34.setCellValue(messageSource.getMessage("column.cuspocode", locale));
		
		HSSFCell cell35 = row0.createCell(33);
		cell35.setCellValue(messageSource.getMessage("column.cusprdcode", locale));
		
		HSSFCell cell36 = row0.createCell(34);
		cell36.setCellValue(messageSource.getMessage("column.cusprdname", locale));
		
		HSSFCell cell37 = row0.createCell(35);
		cell37.setCellValue(messageSource.getMessage("column.cusprdspec", locale));
	}

	public void exportExcel(HttpServletResponse response, HSSFWorkbook hssfWorkbook, String tableName) {
		OutputStream out = null;
		try {
//			response.setContentType("application/force-download;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//			response.setHeader("Content-Disposition",
//					"attachment;fileName=" + new String(tableName.getBytes(), "ISO-8859-1"));
			out = response.getOutputStream();
			hssfWorkbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
			FileUploadUtils.writeAsyncCallback(response, "", 1);
		} finally {
			try {
				if (out != null) {
					out.flush();
					out.close();
				}
				if (hssfWorkbook != null) {
					hssfWorkbook.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
				FileUploadUtils.writeAsyncCallback(response, "", 1);
			}
		}
	}

	/**
	 ** 更新出货单对应的数据
	 * @param bsCustShipmentDto
	 * @param paramUtils
	 * @return
	 */
	public EditDataResult<BsCustShipmentForm> update(BsCustShipmentDto bsCustShipmentDto, ParamUtils paramUtils) {
		String pmnCode = bsCustShipmentDto.getPmnCode();
		String moCode = bsCustShipmentDto.getMoCode();
		String sn = bsCustShipmentDto.getMainSn();
		try {
			if(!StringUtils.isEmpty(pmnCode)) {
				updateByPmnCode(pmnCode);
			}else if (!StringUtils.isEmpty(moCode)) {
				updateByMoCode(moCode);
			}else if(!StringUtils.isEmpty(sn)) {
				
			}
		} catch (BaseException e) {
			throw e;
		}
		return new EditDataResult<BsCustShipmentForm>();
	}

	public void updateByPmnCode(String pmnCode) {
		String WMS_MN_SQL = "SELECT * FROM WMS_MN WEHRE PMN_CODE = ? ";
		WmsMnDto wmsMnDto = sqlQueryUtils.get(WMS_MN_SQL, new Object[] {pmnCode}, WmsMnDto.class);
		if(wmsMnDto != null) {
			MesCustShipmentDto mesCustShipmentDto = new MesCustShipmentDto();
			mesCustShipmentDto.setPmnCode(pmnCode);
			List<MesCustShipmentDto> select = mesCustShipmentService.select(mesCustShipmentDto);
			if(select.size() > 0) {
				for (MesCustShipmentDto mesCustShipmentDto2 : select) {
					String id = mesCustShipmentDto2.getId();
					//删除明细表
					String delSql = "delete from MES_CUST_SHIPMENT_DT where PID = ? ";
					jdbcTemplate.update(delSql, new Object[] {id});
					
				}
				mesCustShipmentService.deleteAllByPrimaryKey(select);
			}
			
			String pmnSql = BsCustShipmentForPmnCodeService.PMN_SQL;
			Date shipDate = wmsMnDto.getShipDate();
			Date createdDate = wmsMnDto.getCreatedDate();
			Date beginDate = DateUtils.addDays(createdDate, -365);
			Date endDate = DateUtils.addDays(createdDate, 365);
			List<BsMnLpnForm> bsMnLpnDtos = sqlQueryUtils.queryAll(pmnSql, new Object[] { pmnCode, beginDate, endDate }, BsMnLpnForm.class);
			for (BsMnLpnForm bsMnLpnDto : bsMnLpnDtos) {
				WmsCustShipmentDto wmsCustShipmentDto = new WmsCustShipmentDto();
				BigDecimal lpnInitQty = bsMnLpnDto.getLpnInitQty();
				if(lpnInitQty!=null && lpnInitQty.compareTo(BigDecimal.ONE) > 0) {
					wmsCustShipmentDto.setmBoxCode(bsMnLpnDto.getLpn());
				}else {
					wmsCustShipmentDto.setTfSn(bsMnLpnDto.getLpn());
				}
				wmsMnProdSellByLpnPdaService.saveNewMesDatas(wmsCustShipmentDto, wmsMnDto);
			}
			
		}
	}
	
	/**
	 * 工单删除出货信息， 通过工单获取查询到每个SN， 并将其更新
	 * @param moCode
	 */
	public void updateByMoCode(String moCode) {
		String sql = "SELECT ID, TF_SN, MAIN_SN, M_BOX_CODE from MES_CUST_SHIPMENT WHERE MO_CODE = ? ";
		List<MesCustShipmentDto> mesCustShipmentDtoList = sqlQueryUtils.queryAll(sql, new Object[] {moCode}, MesCustShipmentDto.class);
		for (MesCustShipmentDto mesCustShipmentDto : mesCustShipmentDtoList) {
			String id = mesCustShipmentDto.getId();
			String tfSn = mesCustShipmentDto.getTfSn();
			String mainSn = mesCustShipmentDto.getMainSn();
			String mBoxCode = mesCustShipmentDto.getMBoxCode();
			String sn = null;
			if(!StringUtils.isEmpty(tfSn)) {
				sn = tfSn;
			}else {
				sn = mainSn;
			}
			
			String sqlPmnString =  "SELECT WM.PMN_CODE, WM.SHIP_DATE "
					+ "FROM  BS_MN_LPN N "
					+ "INNER JOIN WMS_MN WM ON N.MN_ID = WM.PMN_CODE "
					+ "WHERE N.LPN = ? ";
			BsCustShipmentForm wmsMn = sqlQueryUtils.get(sqlPmnString, new Object[] {mBoxCode}, BsCustShipmentForm.class);
			if(wmsMn == null) {
				//没有出货单对应信息，删除
				mesCustShipmentService.deleteByPrimaryKey(mesCustShipmentDto);
				String delSql = "delete from MES_CUST_SHIPMENT_DT where PID = ?";
				Object[] delObjects= new Object[] {id};
				jdbcTemplate.update(delSql, delObjects);
				continue ;
			}
			
			String sqlLbId =  "SELECT M.MO_ID, M.LB_ID, M.LB_ID AS FIRST_LB_ID, O.ERP_MO, O.MO_CODE, O.MO_TYPE,"
			+ "G.AGE_PROD_TYPE, P.TB_DATE, M.WP_PEDATE AS FINISH_DATE, M.CREATED_DATE, "
			+ "L.MTRL_NAME AS PROD_NAME, L.MTRL_DESC AS PROD_DESC , L.MTRL_CODE AS PROD_CODE  "
			+ "FROM MES_MO_LB M " 
			+ "INNER JOIN BS_MO O ON O.MO_ID = M.MO_ID AND O.MO_CODE = ? "
			+ "LEFT JOIN MES_PROD_EXCH_CONFIG G ON G.PROD_ID = O.PROD_ID AND G.MO_ID IS NULL "
			+ "LEFT JOIN BS_MO_PROG P ON P.MO_ID = O.MO_ID "
			+ "LEFT JOIN BS_MTRL L ON L.MTRL_ID = O.PROD_ID "
			+ "WHERE M.LB_ID = ? ";
			List<BsCustShipmentForm> queryAll = sqlQueryUtils.queryAll(sqlLbId, new Object[] {moCode, sn}, BsCustShipmentForm.class);
			if(queryAll != null && queryAll.size() > 0) {
				BsCustShipmentForm bsCustShipmentForm = queryAll.get(0);
				bsCustShipmentForm.setmBoxCode(mBoxCode);
 
				BsCustShipmentForm pmnForm = wmsMn;
				String pmnCode = pmnForm.getPmnCode();
				Date shipDate = pmnForm.getShipDate();
				Date addMonths = DateUtils.addMonths(shipDate, 12);
				bsCustShipmentForm.setWarrantyTime("12个月");
				bsCustShipmentForm.setWarrantyUpTime(addMonths);
				bsCustShipmentForm.setPmnCode(pmnCode);
				bsCustShipmentForm.setShipDate(shipDate);

				
				BigDecimal queryAgeTime = this.queryAgeTime(bsCustShipmentForm);
				bsCustShipmentForm.setAgeTime(queryAgeTime);
				
				String ageProdType = bsCustShipmentForm.getAgeProdType();
				if(StringUtils.isBlank(ageProdType)) {
					ageProdType = "-1";
				}
				String lbId = bsCustShipmentForm.getLbId();
				// 设置SN
				this.setSn(ageProdType, bsCustShipmentForm, lbId);
				// 查询称重
				bsCustShipmentForPmnCodeService.queryWeight(bsCustShipmentForm);
				switch (ageProdType) {
				case "1": //整机
					// 查询老化数据
					this.queryAcclbInOhterMoDev(bsCustShipmentForm, bsCustShipmentForm.getMoId());
					break;
				case "2"://整件
					// 查询老化数据
					this.queryAcclbInOhterMoDev(bsCustShipmentForm, bsCustShipmentForm.getMoId());
					break;
				case "3":
					// 查询IPC
					this.queryAcclbInOhterMoIpcDev(bsCustShipmentForm, bsCustShipmentForm.getMoId());
					break;
					
				default:
					break;
				}
				//查询附属标签
				bsCustShipmentForPmnCodeService.queryAccLb(bsCustShipmentForm);
				
				List<BsCustShipmentForm> temp = new ArrayList<BsCustShipmentForm>(1);
				temp.add(bsCustShipmentForm);
				List<BsCustShipmentForm> data = this.judgeAccLbInfo(temp, ageProdType, bsCustShipmentForm.getMoId());
				if(data != null && data.size() > 0) {
					//删除,插入
					mesCustShipmentService.deleteByPrimaryKey(mesCustShipmentDto);
					String delSql = "delete from MES_CUST_SHIPMENT_DT where PID = ?";
					Object[] delObjects= new Object[] {id};
					jdbcTemplate.update(delSql, delObjects);
					
					//单个SN，合并所有物料
					MesCustShipmentDto mesCustShipmentDb =  null;
					ArrayList<Object[]> mateList = new ArrayList<Object[]>();
					if(data.size() == 1) {
						BsCustShipmentForm bsCustForm = data.get(0);
						mesCustShipmentDb =  wmsMnProdSellByLpnPdaService.getOneMesCustData(bsCustForm,mateList);
					}else {
						mesCustShipmentDb = wmsMnProdSellByLpnPdaService.getMesFormData(0, data.size()-1, data, mateList);
					}
					mesCustShipmentService.insertNotNull(mesCustShipmentDb);
					if(mateList.size() > 0) {
						wmsMnProdSellByLpnPdaService.insertMateDatas(mateList);
					}
				}
			}else {
				//删除无效数据
				mesCustShipmentService.deleteByPrimaryKey(mesCustShipmentDto);
				String delSql = "delete from MES_CUST_SHIPMENT_DT where PID = ?";
				Object[] delObjects= new Object[] {id};
				jdbcTemplate.update(delSql, delObjects);
			}
		}
	}

	@Override
	public JqGridResponse<BsCustShipmentForm> query(JqGridRequest<BsCustShipmentForm> request, ParamUtils paramUtils) {
		return this.query(request);
	}

	@Override
	public void importConvert(int i, List<BsCustShipmentForm> list, ParamUtils paramUtils) {
	}

	@Override
	public void exportConvert(int j, List<BsCustShipmentForm> list, ParamUtils paramUtils) {
	}

	@Override
	public List<String> importByExcel(int i, List<BsCustShipmentForm> list, ParamUtils paramUtils) {
		List<String> errMsgArray = new ArrayList<String>();// 保存异常
		return errMsgArray;
	}

	@Override
	public <T> EditResult verifyData(Map<String, Object> rowData, int i, ParamUtils paramUtils, JsonRequest<T> extend) {
		List<String> errMsgArray = new ArrayList<String>();// 保存异常
		EditResult result = new EditResult();
		Map<String, Object> extendMap = new HashMap<String, Object>();
		extendMap.put("rowData", rowData);
		
		JqGridRequest<BsCustShipmentForm> request = new JqGridRequest<BsCustShipmentForm>();
		List<ConditionDto> conditionList = new ArrayList<ConditionDto>();
		if(rowData.get("mac") != null && StringUtils.isNotBlank(rowData.get("mac").toString())) {
			ConditionDto conditionDto = new ConditionDto();
			conditionDto.setColumnSign("=");
			conditionDto.setColumnValue(rowData.get("mac").toString());
			conditionDto.setConditionColumn("mac");
			conditionDto.setConditionSign("and");
			conditionList.add(conditionDto);
		}
		if(rowData.get("imei") != null && StringUtils.isNotBlank(rowData.get("imei").toString())) {
			ConditionDto conditionDto = new ConditionDto();
			conditionDto.setColumnSign("=");
			conditionDto.setColumnValue(rowData.get("imei").toString());
			conditionDto.setConditionColumn("imei");
			conditionDto.setConditionSign("and");
			conditionList.add(conditionDto);
		}
		if(rowData.get("mainChipNo") != null && StringUtils.isNotBlank(rowData.get("mainChipNo").toString())) {
			ConditionDto conditionDto = new ConditionDto();
			conditionDto.setColumnSign("=");
			conditionDto.setColumnValue(rowData.get("mainChipNo").toString());
			conditionDto.setConditionColumn("chipNumber");
			conditionDto.setConditionSign("and");
			conditionList.add(conditionDto);	
		}
		if(rowData.get("tfSn") != null && StringUtils.isNotBlank(rowData.get("tfSn").toString())) {
			ConditionDto conditionDto = new ConditionDto();
			conditionDto.setColumnSign("=");
			conditionDto.setColumnValue(rowData.get("tfSn").toString());
			conditionDto.setConditionColumn("sn");
			conditionDto.setConditionSign("and");
			conditionList.add(conditionDto);
		}else if(rowData.get("mainSn") != null && StringUtils.isNotBlank(rowData.get("mainSn").toString())) {
			ConditionDto conditionDto = new ConditionDto();
			conditionDto.setColumnSign("=");
			conditionDto.setColumnValue(rowData.get("mainSn").toString());
			conditionDto.setConditionColumn("sn");
			conditionDto.setConditionSign("and");
			conditionList.add(conditionDto);
		}
		request.setCondition(conditionList);
		List<JqGridRequest<BsCustShipmentForm>> list = importData.get(thisRequest.toString()) == null? new ArrayList<JqGridRequest<BsCustShipmentForm>>():importData.get(thisRequest.toString());
		list.add(request);
		importData.put(thisRequest.toString(), list);
		extendMap.put("errMsgArray", errMsgArray);
		result.setExtend(extendMap);
		return result;
	}

	@Override
	public CheckExcelResult checkCommonData(Map<Integer, Map<String, Object>> rowDatas,
			Map<Integer, List<String>> errMsgs, ParamUtils paramUtils, JsonRequest<BsCustShipmentForm> extend) {
		return null;
	}
	
	
	public List<BsCustShipmentForm> mySearch(JsonRequest<BsCustShipmentForm> jsonRequest){ 
		List<JqGridRequest<BsCustShipmentForm>> list = importData.get(thisRequest.toString());
		importData.remove(thisRequest.toString());
		List<BsCustShipmentForm> result = new ArrayList<BsCustShipmentForm>();
		if(list != null) {
			for(JqGridRequest<BsCustShipmentForm> jq : list) {
	 			List<BsCustShipmentForm> thisSearch = search(jq);
	 			if(thisSearch != null && thisSearch.size() > 0) {
	 				result.addAll(thisSearch);
	 			}else {
	 				//查询不到结果时候调用查询老mes表信息
	 				List<BsCustShipmentForm> oldList = oldMesSearch(jq);
	 				if(oldList != null && oldList.size() > 0) {
	 					result.addAll(oldList);
	 				}else {
	 					//都查询不到时候直接返回原查询参数
		 				BsCustShipmentForm bsCustShipmentForm = jqRequestToForm(jq);
		 				result.add(bsCustShipmentForm);
	 				}
	 			}
			}
		}
		return result;
	}
	
	
	public List<BsCustShipmentForm> oldMesSearch(JqGridRequest<BsCustShipmentForm> jqGridRequest){
		BsCustShipmentForm bsCustShipmentForm = jqRequestToForm(jqGridRequest);
		String sql = "select * from mes_cust_shipment where 1=1 ";
		if(StringUtils.isNotBlank(bsCustShipmentForm.getTfSn())) {
			sql += " and (main_sn = '"+bsCustShipmentForm.getTfSn()+"' or tf_sn = '"+bsCustShipmentForm.getTfSn()+"') ";
		}
		if(StringUtils.isNotBlank(bsCustShipmentForm.getMac())) {
			sql += " and mac = '"+bsCustShipmentForm.getMac()+"' ";
		}
		if(StringUtils.isNotBlank(bsCustShipmentForm.getMainChipNo())) {
			sql += " and main_chip_no = '"+bsCustShipmentForm.getMainChipNo()+"' ";
		}
		if(StringUtils.isNotBlank(bsCustShipmentForm.getImei())) {
			sql += " and imei = '"+bsCustShipmentForm.getImei()+"' ";
		}
		List<BsCustShipmentForm> list = sqlQueryUtils.queryAll(sql, null, BsCustShipmentForm.class);
		if(list != null && list.size() > 0) {
			for(BsCustShipmentForm bsForm : list) {
				if(StringUtils.isNotBlank(bsForm.getTfSn()) || StringUtils.isNotBlank(bsForm.getMainSn())) {
					String linkSql =  "select DELIVERYBILLNO as pmn_code, to_date(DELIVERYDATE, 'yyyy-mm-dd') as ship_date from APPS.CUX_MES_SHIP_HIS@EBSDBLINK "
							+ "where 1=1 and ("
							+ (StringUtils.isNotBlank(bsForm.getTfSn()) ?("PRODUCTIONSN = '"+bsForm.getTfSn())+"' OR MATERIELSN = '"+bsForm.getTfSn()+"' ":"" )
							+ (StringUtils.isNotBlank(bsForm.getMainSn()) ?("PRODUCTIONSN = '"+bsForm.getMainSn())+"' OR MATERIELSN = '"+bsForm.getMainSn()+"' ":"" )
							+ ")";
					List<BsCustShipmentForm> linkSqlList = sqlQueryUtils.queryAll(linkSql, null, BsCustShipmentForm.class);
					if(linkSqlList != null && linkSqlList.size() > 0) {
						bsForm.setPmnCode(linkSqlList.get(0).getPmnCode());
						bsForm.setShipDate(linkSqlList.get(0).getShipDate());
					}
				}
			}
		}
		return list;
	}
	
	public BsCustShipmentForm jqRequestToForm(JqGridRequest<BsCustShipmentForm> jqGridRequest) {
		List<ConditionDto> conditionList = jqGridRequest.getCondition();
		BsCustShipmentForm bsCustShipmentForm = new BsCustShipmentForm();
		for(ConditionDto condition : conditionList) {
			if(StringUtils.isNotBlank(condition.getColumnSign())) {
				if("sn".equals(condition.getConditionColumn())) {
					bsCustShipmentForm.setTfSn(condition.getColumnValue());
				}
				if("mac".equals(condition.getConditionColumn())) {
					bsCustShipmentForm.setMac(condition.getColumnValue());
				}
				if("chipNumber".equals(condition.getConditionColumn())) {
					bsCustShipmentForm.setMainChipNo(condition.getColumnValue());
				}
				if("imei".equals(condition.getConditionColumn())) {
					bsCustShipmentForm.setImei(condition.getColumnValue());
				}
			}
		}
		return bsCustShipmentForm;
	}
}
